Scribd
Upload
4 views2 pages

Dax Nested Functions Guide

This guide explains the use of nested DAX functions in Power BI, emphasizing that many functions cannot be used alone and require combinations with others. It details various DAX functions such as AND(), FILTER(), and ALL(), along with their return types and typical usage scenarios. The document concludes by advising users to focus on understanding return types and contexts to improve their DAX skills effectively.

Uploaded by

Kanishk Bhagat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views2 pages

Dax Nested Functions Guide

This guide explains the use of nested DAX functions in Power BI, emphasizing that many functions cannot be used alone and require combinations with others. It details various DAX functions such as AND(), FILTER(), and ALL(), along with their return types and typical usage scenarios. The document concludes by advising users to focus on understanding return types and contexts to improve their DAX skills effectively.

Uploaded by

Kanishk Bhagat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DAX Nested Functions & Return Types Guide

This guide explains why many DAX functions cannot be used alone and how they are combined with other functions
in real Power BI calculations.

Logical Functions

AND(), OR(), and NOT() return TRUE/FALSE values and are usually used inside IF(), FILTER(), or CALCULATE().
High Profit Sales =
IF(
AND(Sales[Amount] > 1000, Sales[Profit] > 100),
"Good",
"Bad"
)

FILTER()

FILTER() returns a table, not a final value. It is usually wrapped by CALCULATE(), COUNTROWS(), or SUMX().
High Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(Sales, Sales[Amount] > 1000)
)

ALL()

ALL() removes filters and is commonly used inside CALCULATE().


Total Sales Ignore Filters =
CALCULATE(
SUM(Sales[Amount]),
ALL(Sales)
)

VALUES()

VALUES() returns a unique list table and is often wrapped with COUNTROWS() or CONCATENATEX().
Unique Cities =
COUNTROWS(
VALUES(Customer[City])
)

DATESYTD()
DATESYTD() returns a date table used with CALCULATE().
YTD Sales =
CALCULATE(
SUM(Sales[Amount]),
DATESYTD(Date[Date])
)

SUMX()

Iterator functions require both a table and an expression.


Total Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[Price]
)

DAX Return Types

Return Type Examples

Scalar Value SUM, MAX, DIVIDE


Logical Value AND, OR, NOT
Table FILTER, VALUES, DATESYTD, ALL

The fastest way to improve at DAX is to stop memorizing formulas blindly and instead understand: 1. What each
function returns. 2. Which functions can accept that return type. Once you think in return types and context, DAX
becomes much easier to reason about.

You might also like