5. Use DAX iterator functions in Power BI Desktop models

family of functions known as iterator functions (IF):

• enumerate all rows of a table and evaluate a given expression for each row.

single column summarization function like

• sum
• count
• min
• max

are syntactical sugar / decorator of iterator functions like

• sumx
• countx
• minx
• maxx

you must pass a table and an expression to IF

• avoid using large tables (of rows) with expressions that use expansive DAX functions
  • e.g. SEARCH result in slow performance
  • LOOKUPVALUE lead to slow row by row retrieval of values
    • (use RELATED instead)

what is a dax expression?

An expression can contain any or all of the following: operators, constants, or references to columns.

average ignores blank rows from total number of rows

AVERAGE and AVERAGEX does not equal to SUM( Amount ) / Number Of Instances. This is a misconception. If [Amount] is BLANK, this amount is not considered. So, fir instance, AVERAGE of (1, 1, BLANK, BLANK) = 2/2, not 2/4. If you want to force the recognition of BLANKs, you have to add 0 to them. So, AVERAGE of (1, 1, BLANK + 0, BLANK + 0) = 2/4.

use aggregation iterator functions

Discount =
SUMX(
    Sales,
    Sales[Order Quantity]
    * (
        RELATED('Product'[List Price]) - Sales[Unit Price]
    )
)

Higher grain summarization

https://www.coursera.org/articles/data-granularity Low (course) granularity data is more summarized and consists of more extensive, aggregated units. high (fine) granularity, you will break down your data into very small levels of grain e.g. high: keystroke mid: number of times someone saved or edited their text low: entire essay or submission

each value point in a visual should correspond to an eval at a filter context DAX A

Revenue Avg =
AVERAGEX(
    Sales,
    Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct])
)

DAX B

Revenue Avg Order =
AVERAGEX(
    VALUES('Sales Order'[Sales Order]),
    [Revenue]
)

DAX B is higher than DAX A because it is higher up in the hierarchy sale order contain multiple sale lines.

Filter context and the VALUES function are introduced in the filter context module. #TODO what module???

Calculate ranks

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

order default desc (0)

ties: dense vs skip ( )

It’s not appropriate to rank total products, so use the following logic to return BLANK, unless a single product is filtered: DAX

Product Quantity Rank =
IF(
    HASONEVALUE('Product'[Product]),
    RANKX(
        ALL('Product'[Product]),
        [Quantity],
        ,
        ,
        DENSE
    )
)

hasonevalue test whether the Product column in the Product table has a single value in filter context. It’s the case for each product group, but not for the total, which represents all products.