Power BI - Questions & Answers | Business Analytics & Intelligence | Processes & Tools | Part 5

 

Question: State all the commonly used DAX Functions in Power BI.

Suggested Answer:

Data Analysis Expressions (DAX) is the native formula and query language for Microsoft PowerPivot, Power BI Desktop and SQL Server Analysis Services (SSAS) Tabular models. DAX includes some of the functions that are used in Excel formulas with additional functions that are designed to work with relational data and perform dynamic aggregation.

DAX data types

• Integer
• Real
• Currency
• Date (datetime)
• TRUE/FALSE (Boolean)
• String
• Variant

Note: The BLOB (binary large object) data type is managed by the Tabular model but cannot be directly manipulated by DAX expressions.

Most Common  DAX Function Types with examples

1. Aggregation Functions

Calculate sums, averages, counts, etc.

SUM():

Total Sales = SUM(Sales[Amount])  // Sum of sales amounts

AVERAGE():

Avg Price = AVERAGE(Products[Price])  // Average product price

COUNTROWS():

Order Count = COUNTROWS(Orders)  // Number of orders

2. Filter Functions

Manipulate filter contexts.

FILTER():

High Sales = CALCULATE(SUM(Sales[Amount]), Sales[Amount] > 1000)

ALL():

% of Total Sales = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales)))

RELATED():

Product Category = RELATED(Products[Category])  // Pulls data from a related table

3. Time Intelligence Functions

Analyze data over time.

TOTALYTD():

YTD Sales = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

SAMEPERIODLASTYEAR():

Sales YoY Growth = 

VAR PY_Sales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

RETURN (SUM(Sales[Amount]) - PY_Sales) / PY_Sales

DATEADD():

Sales Next Month = CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], 1, MONTH))

4. Logical Functions

Conditional logic.

IF():

Profit Status = IF([Profit] > 0, "Positive", "Negative")

SWITCH():

Region Group = SWITCH([Region], "North", "Group A", "South", "Group B", "Other")

AND()/OR():

High Priority = IF(AND([Urgency] = "High", [Status] = "Open"), "Yes", "No")

5. Text Functions

String manipulation.

CONCATENATE():

Full Name = CONCATENATE(Customers[FirstName], " ", Customers[LastName])

LEFT()/RIGHT():

Short Code = LEFT(Products[ProductID], 3)  // First 3 characters

SEARCH():

Has Discount = IF(SEARCH("Disc", Promotions[Description]) > 0, "Yes", "No")

6. Math & Trigonometric Functions

Numerical operations.

ROUND():

Rounded Sales = ROUND(Sales[Amount], 2)  // Rounds to 2 decimal places

DIVIDE():

Profit Margin = DIVIDE([Profit], [Revenue], 0)  // Safe division (avoids errors)

7. Statistical Functions

Advanced calculations.

STDEV.P():

Sales Std Dev = STDEV.P(Sales[Amount])  // Standard deviation

RANKX():

Sales Rank = RANKX(ALL(Customers), [Total Sales], , DESC)

8. Parent-Child Functions

Hierarchy handling (e.g., organizational charts).

PATH():

Employee Hierarchy = PATH(Employees[EmployeeID], Employees[ManagerID])

9. Table Functions

Create/manipulate tables.

SUMMARIZE():

Sales by Region = SUMMARIZE(Sales, Sales[Region], "Total Sales", SUM(Sales[Amount]))

ADDCOLUMNS():

Products with Profit = ADDCOLUMNS(Products, "Profit", [Price] - [Cost])

10. Information Functions

Check data types or states.

ISBLANK():

Valid Check = IF(ISBLANK([Discount]), "No Discount", "Has Discount")

HASONEFILTER():

Is Filtered = IF(HASONEFILTER(Products[Category]), "Single Filter", "Multiple Filters")

For more information, please visit Documentation on Microsoft DAX.