Basic Functions in Power BI

SUMX : This function is used to evaluate an expression for each row. It's useful when you need to perform calculations on a row-by-row basis before aggrega…

Author

SQLDataDev Editorial Team

Mar 19, 2026 6 min read

SUMX: This function is used to evaluate an expression for each row. It's useful when you need to perform calculations on a row-by-row basis before aggregating the results.

SUMX(<table>, <expression>)
TotalRevenue = SUMX('Sales by Store', 'Sales by Store'&#091;Quantity] * 'Sales by Store'&#091;UnitPrice]) 

-- other method 

-- Add new column 
Cost = 'Sales by Store'&#091;Quantity] * 'Sales by Store'&#091;UnitPrice]
-- after cost we get Total cost by use of sum function

Total Cost = sum(Cost)

-- if we Added new column then we increased dataset size which are not good.

-- used sumx function for this.

RELATED: RELATED function is used to retrieve a value from a related table. It's often used in scenarios where you have multiple tables connected through relationships and need to fetch data from one table to another based on those relationships, relationship between the current table and the related table established in the data model.

For example, consider two tables: Orders and Customers. There's a relationship between them based on the CustomerID column. If you want to get the CustomerName from the Customers table into the Orders table, you can use RELATED like this:

CustomerName = RELATED(Customers&#091;CustomerName])

-- This formula will fetch the CustomerName associated with the CustomerID in the Orders table from the Customers table.

Keep in mind that RELATED only works within the context of established relationships in the data model. If there's no relationship or the relationship is not correctly defined, RELATED won't return the expected results.

RELATEDTABLE: This function returns all the rows from a related table.

Total Cost = SUMX('Sales by Store',
                  'Sales by Store'&#091;quantity_sold] * RELATED('Product Lookup'&#091;current_cost]))

IFERROR: It allows you to specify a value or expression to be returned if an error occurs, otherwise, it returns the result of the expression being evaluated.

IFERROR(<expression>, <value_if_error>)

-- Handling Division by Zero Error:

AverageRevenuePerOrder = IFERROR( SUM(Sales&#091;Revenue]) / COUNTROWS(Sales), 0 )

-- Handling Invalid Date Format:

CleanedDate = IFERROR( DATE(LEFT(Sales&#091;Date],4), MID(Sales&#091;Date],5,2), RIGHT(Sales&#091;Date],2)), "N/A" )

-- Handling Errors in Lookup Values:

CustomerName = IFERROR( RELATED(Customers&#091;CustomerName]), "Unknown" )

Used DAX variables

% Quantity Sold to Females = 
VAR TotalOrders_Female =
CALCULATE(
    SUM(
        'Sales by Store'&#091;quantity_sold]
    ),
    FILTER(
        'Customer Lookup',
        'Customer Lookup'&#091;gender] = "F"
    )
)
VAR QuantitySold = 
SUM(
    'Sales by Store'&#091;quantity_sold]
)
VAR Ratio = 
DIVIDE(
    TotalOrders_Female,
    QuantitySold,
    "-"
)
RETURN
Ratio

Function	Description	Syntax	Example	Sample Output
INT()	Rounds a number down to the nearest integer	`=INT(Number)`	`=INT(5.9)`	`5`
ROUND()	Rounds a number to a specific number of digits	`=ROUND(Number, NumberOfDigits)`	`=ROUND(5.678, 2)`	`5.68`
ROUNDUP()	Rounds a number up, away from zero	`=ROUNDUP(Number, NumberOfDigits)`	`=ROUNDUP(5.123, 2)`	`5.13`
ROUNDDOWN()	Rounds a number down, toward zero	`=ROUNDDOWN(Number, NumberOfDigits)`	`=ROUNDDOWN(5.987, 2)`	`5.98`
MROUND()	*Rounds a number to the desired multiple*	`=MROUND(Number, Multiple)`	`=MROUND(10, 3)`	`9`
TRUNC()	Truncates a number to an integer by removing the decimal part of the number	`=TRUNC(Number, )`	`=TRUNC(5.678)`	`5` With out round off number extract
FIXED()	Rounds number down to specified number of decimals and returns result as text	`=FIXED(Number, , )`	`=FIXED(1234.567, 2, TRUE)`	`1234.57`
CEILING()	Rounds a number up, to the nearest integer or nearest unit of significance	`=CEILING(Number, Significance)`	`=CEILING(5.5, 1)`	`6`
FLOOR()	Rounds a number down, toward zero, to the nearest multiple of significance	`=FLOOR(Number, Significance)`	`RoundedDownToMultiple = FLOOR(23, 5)`	`This will return 20, as 20 is the nearest multiple of 5 that is less than or equal to 23.`

MROUND Equivalent = 
    VAR ValueToRound = &#091;Value]
    VAR Multiple = &#091;Multiple]
    RETURN
        FLOOR(ValueToRound / Multiple, 1) * Multiple


TimeRoundUpFor15mintue =     
VAR ValueToRound = TimeSheet&#091;PunchIn]    
VAR Multiple = "0:15"    
RETURN        
FLOOR(ValueToRound / Multiple,1) * Multiple

Function	Description	Syntax	Example	Sample Output
ISBLANK()	Checks whether a value is blank, and returns TRUE or FALSE	`=ISBLANK(Value)`	`=ISBLANK(Sales)`	`TRUE` or `FALSE`
ISERROR()	Checks whether a value is an error, and returns TRUE or FALSE	`=ISERROR(Value)`	`=ISERROR(Sales)`	`TRUE` or `FALSE`
ISLOGICAL()	Checks whether a value is a logical value (TRUE or FALSE), and returns TRUE or FALSE	`=ISLOGICAL(Value)`	`=ISLOGICAL(TRUE)`	`TRUE`
ISNUMBER()	Checks whether a value is a number, and returns TRUE or FALSE	`=ISNUMBER(Value)`	`=ISNUMBER(Sales)`	`TRUE` or `FALSE`
ISNONTEXT()	Checks whether a value is not text (blank cells are not text), and returns TRUE or FALSE	`=ISNONTEXT(Value)`	`=ISNONTEXT(Sales)`	`TRUE` or `FALSE`
ISTEXT()	Checks whether a value is text, and returns TRUE or FALSE	`=ISTEXT(Value)`	`=ISTEXT(Sales)`	`TRUE` or `FALSE`

Function	Description	Syntax	Example	Sample Output
CURRENCY()	Evaluates the argument and returns the result as a currency data type	`=CURRENCY(Value)`	`=CURRENCY(1000.75)`	`$1,000.75`
FORMAT()	Converts a value to text in the specified number format	`=FORMAT(Value, Format)`	`=FORMAT(TODAY(), "MM/DD/YYYY")`	`"06/08/2024"`
DATE()	Returns the specified date in datetime format	`=DATE(Year, Month, Day)`	`=DATE(2024, 6, 8)`	`06/08/2024`
TIME()	Converts hours, minutes, and seconds given as numbers to a time in datetime format	`=TIME(Hours, Minute, Second)`	`=TIME(14, 30, 0)`	`2:30:00 PM`
DATEVALUE()	Converts a date in the form of text to a date in datetime format	`=DATEVALUE(DateText)`	`=DATEVALUE("06/08/2024")`	`06/08/2024`
VALUE()	Converts a text string that represents a number to a number	`=VALUE(Text)`	`=VALUE("1234.56")`	`1234.56`

Function	Description	Syntax	Example	Sample Output
COALESCE()	Returns the first non-blank value among its arguments	`=COALESCE(Value1, Value2, ...)`	`=COALESCE(Sales, 0)`	If `Sales` is blank, returns `0`; otherwise, returns `Sales`
IF()	Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE	`=IF(LogicalTest, ResultIfTrue, ResultIfFalse)`	`=IF(Sales > 200, "High", "Low")`	If `Sales` is greater than 200, returns "High"; otherwise, returns "Low"
SWITCH()	Evaluates an expression against a list of values and returns the result corresponding to the first matching value	`=SWITCH(Expression, Value1, Result1, , )`	`=SWITCH(Sales, "A", 1, "B", 2, "C", 3, 0)`	If `Sales` is "A", returns 1; if "B", returns 2; if "C", returns 3; otherwise, returns 0

--Switch uses

Quarter & Year = 
VAR Q1 = 'Calendar'&#091;Month_ID] IN {1,2,3} -- declare condition here 
VAR Q2 = 'Calendar'&#091;Month_ID] IN {4,5,6}
VAR Q3 = 'Calendar'&#091;Month_ID] IN {7,8,9}
VAR Q4 = 'Calendar'&#091;Month_ID] IN {10,11,12}

RETURN
SWITCH(
    TRUE(),
    Q1, "Q1" & "-" & 'Calendar'&#091;Year_ID], --Q1 variable work as a Condition
    Q2, "Q2" & "-" & 'Calendar'&#091;Year_ID],
    Q3, "Q3" & "-" & 'Calendar'&#091;Year_ID],
    Q4, "Q4" & "-" & 'Calendar'&#091;Year_ID],
    "-"
)

How to create comma separate value: Used CONCATENATEX for that.

--Show selected values:

Label = "Your choose value is " & 
CONCATENATEX(
    VALUES('Product'&#091;product_category]),
    'Product'&#091;product_category],
    ",",
    'Product'&#091;product_category],
    ASC
)

HASONEVALUE:

Scenario

Imagine you have a data model with two tables:

Product Table: Contains information about product categories.
Sales Table: Contains sales data.

Product Table

ProductID	Category
1	Electronics
2	Furniture
3	Clothing
Sales Table

SaleID	ProductID	Amount
1	1	100
2	2	200
3	1	150
4	3	300

Is Single Category = 
IF(
    HASONEVALUE(Product&#091;Category]),
    "Yes",
    "No"
)

Result

When you filter your visual to show only one category (e.g., Electronics), the measure will return "Yes".
When you filter your visual to show multiple categories (e.g., Electronics and Furniture), the measure will return "No".

The HASONEVALUE function helps you determine if there's only one distinct value in a column within the current context. This is useful for conditional calculations and ensuring your data displays correctly based on the context.

Topics covered

Power BI

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts