DA.PBI-DAX Guide (Part 1)

: 160dd760d2dd78 Power BI Overview Notes

Basic DAX syntax

• Quoting the column in the table (if it is not the beginning of a number, does not contain spaces, nor is it a reserved word, you can omit the single quotes) Example:

  'Sales'[Quantity]

Best practice: Always use table names in column references, and always avoid table names in metric references. This allows you to distinguish between measures and columns when reading the code.

• Comment: single-line comment ( // or -- )
• Data type: Integer , Decimal , Currency , DateTime (leap year error, need ≥1900-03-01), Boolean , String , Variant , Binary
• Operator () , +-*/ , comparison operator, && , || , IN , NOT

• Operator overloading, coercion

  • Time, for example: Sales[OrderDatePlusOneWeek] = Sales[Order Date] + 7
  • Text link, for example: = 5 & 4 returns "54"
  • Operator, for example: = "5" + "4" returns 9.
• Table builder: single-column {"Red","Blue","White"} , multi-column {(1,2,3),}
• Conditional statement: IF(exp,true,false)

• Calculated columns and measures

  • Calculated column: Perform row-by-row operation when the memory database is refreshed, occupying space and slowing down the running speed. Generally used to observe the intermediate value during development.

  • Measures: aggregate calculations on multiple rows. (Calculated in the visual filter context or DAX query context)

    Best Practices

    The following operations must define a calculated column

    • Put the calculated results in the slicer, pivot the row area, column area (not the value area) of the table or matrix, or use the calculated column as the filter condition of the DAX query
    • Define an expression that is strictly bound to the current line (for example: price × quantity)
    • When classifying text or numerical values (for example: the age range of customers: 0~18,18~25)

    Define metric values when filtering reports to view results

    • Calculate the profit margin based on the filter conditions of the report
    • In the case of variational and regional filters, calculate the proportion of a product's sales to all product sales

• Variable VAR ... RETURN ... avoids repetitive expressions, improves readability and performance

  • Rules for variable scope (block-level scope):

    • A variable is available in the RETURN part of the VAR/RETURN code block in which it is located, and in the VAR/RETURN code block, all variables defined after this variable can also use it. The VAR/RETURN code block can replace any DAX expression in which variables can be read.
    • Variables are not available outside of the VAR/RETURN code block that defines themselves.

• Error handling (conversion error, calculation error, null value and missing value)

  • Catch the error

    • IFERROR(Sales[Quantity] * Sales[Price], BLANK())
    • ISERROR，ISBLANK
  • Throw error ERROR()
  • Format DAXFormatter.com

• Aggregate functions and iterative functions

  • Aggregate function: calculated on the column. Generally logarithmic value or date, MIN/MAX can be used for text. (SUM, AVERAGE, MIN, MAX, STDEV)

    Sales[DaysToDeliver] = INT( Sales[Delivery Date] - Sales[Order Date])
    AvgDelivery:=AVERAGE(Sales[DaysToDeliver])

  • Iterative function: aggregate expression. (SUMX,MINX,FILTER,ADDCOLUMNS,GENERATE)

    • Generally include at least 2 parameters. 1. The scanned table, 2. Calculate the value expression for each row of the table.

      AvgDelivery:=AVERAGEX(
      Sales , 
      INT( Sales[Delivery Date] - Sales[Order Date])
      )

• Table functions (FILTER, ALL, ALLEXCEPT

  • Iteration table, for example:

    Sales Amount Multiple Items :=SUMX(
     FILTER(Sales,Sales[Quantity]>1),
     Sales[Quantity] * Sales[Net Price]
    )

    [DEFINE {MEASURE <tableName>[<name>] = <expression>}]
    EVALUATE <table>
    [ORDER　BY {<expression>} [{ASC|DESC}]} [, ...]]

  • Null value handling

    • ALL all rows, ignoring any filters. Support multiple columns
    • VALUES all rows, will consider the existing filter, blank rows are valid, support single column
    • DISTINCT returns different values, considers existing filters, does not return empty rows, and supports single column
    • ALLNOBLANKROW supports multiple columns

    Use the table as a scalar value: a table key with a single row and a single column is used like a scalar value

    Brand Name:= IF(
     COUNTROWS (VALUES(Product[Brand]))=1,
     VALUES(Product[Brand])
    )
    ---
    Brand Name:= IF(
     HASONEVALUE (Product[Brand]),
     VALUES(Product[Brand])
    )
    Brand Name:= SELECTEDVALUE (Product[Brand], "Mutiple brands" )

    ALLSELECTED is visible in the current report and only considers all filters except the current visual

    Sales Pct :=DIVIDE(
     SUMX( Sales , Sales[Quantity] * Sales[Net Price] ),
     SUMX( ALL(Sales) , Sales[Quantity] * Sales[Net Price] )
    )
    ---
    Sales Pct :=DVIDE(
     SUMX( Sales , Sales[Quantity] * Sales[Net Price] ),
     SUMX( ALLSELECTED(Sales) , Sales[Quantity] * Sales[Net Price] )
    )

    Valuation context

  • Screening context: Screening model
  • Row context: iteration table

  • Row context for nesting multiple tables

    SUMX(
     'Product Category',
     SUMX(
     RELATEDTABLE('Product'),
     SUMX(
       RELATEDTABLE(Sales),
       Sales[Quantity] * Product[Unit Price] * 'Product Category'[discount]
     )
     )
    )
    ---
    SUMX(
     Sales,
     Sales[Quantity] * RELATED(Product[Unit Price]) * RELATED('Product Category'[Discount])
    )

  • Multi-level nested row context on the same table

    Product[UnitPriceRankDense] = 
     VAR PriceOfCurrentProduct = Product[Unit Price]
     VAR HigherPrices = 
     FILTER (
       VALUES(Product[Unit Price]),
       Product[Unit Price] > PriceOfCurrentProduct
     )
     RETURN COUNTROWS(HigherPrices) + 1
    ---
     Product[UnitPriceRankDense] = COUNTROWS(
     FILTER(
       VALUES(Product[Unit Price]),
       Product[UnitPrice] > EARLIER(Product[Unit Price])
     )
     )

    • Multiple tables:

    • Need to use multiple RELATED functions to access multiple tables on the "one" side of the relationship

      Sales[UnitPriceVariance]=Sales[Unit Price] - RELATED(Product[Unit Price])

    • When the iteration occurs on the "one" end of the relationship, use RELATEDTABLE, which returns all rows corresponding to the "many" end

      Product[NumberOfSales]=COUNTROWS(RELATEDTABLE(Sales ))

CALCULATE function

demand

Contoso GM :=
VAR ContosoSales=
    FILTER (
      Sales,
      RELATED(Product[Brand]) = "Contoso"
    )
VAR ContosoMargin =
  SUMX (
    ContosoSales,
    Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] )
  )
VAR ContosoSalesAmount =
  SUMX(
    ContosoSales,
    Sales[Quantity] * Sales[Net Price]
  )
RETURN DIVIDE (ContosoMargin,ContosoSalesAmount)

Realize with CALCULATE

Gross Margin: = SUMX ( Sales, Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] ))
Sales Amount: = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
GM %: = DIVIDE ( [Gross Margin], [Sales Amount] )

Contoso GM   :=CALCULATE([Gross Margin],Product[Brand]="Contoso")
Contoso GM % :=CALCULATE([GM %],Product[Brand]="Contoso")

• CALCULATE accepts 2 types of filters

  • Tabular list of values

  • Boolean conditions, such as:

    Sales Amount Red Products :=
    CALCULATE(
      [Sales Amount],
      Product[Color]="Red"
    )

dax会转化为值列表的形式。如：

```dax
[Sales Amount Red Products] :=
CALCULATE (
  SUM(Sales[SalesAmount]),
  FILTER(
    ALL(Product[Color]),
    Product[Color]="Red"
  )
)
```

• Summary of CALCULATE rules

  • Overview

    • CALCULATE is executed in the context of valuation. This context contains a filtering context, and may contain one |multiple row contexts. This is the initial environment for formula evaluation
    • CALCULATE creates a new filter context and evaluates the first parameter in it. The new filter context only contains the filter context. Due to the effect of context switching, all row contexts disappear in the filtering context.

    • CALCULATE accepts three types of parameters:

      • The expression to be evaluated in the new filter context. (Always used as the first parameter)
      • A set of display filter parameters that manipulate the original filter context. Each filter parameter may use a modifier (Modiier), such as KEEPFILTERS
      • The CALCULATE adjuster can modify the model and adjust the scope of the original filter context by deleting some filters or changing the relationship structure.
    • When the original context contains one | multiple row contexts, CALCULATE performs context conversion, adding invisible implicit filters. If the row context is provided using the table expression of KEEPFILTERS, then the behavior of the implicit filter will also be modified by KEEPFILTERS.

  • algorithm

    1. CALCULATE calculates all explicit filter parameters in the initial value context, including the original row context (if any) and the original filter context. All explicit filter parameters are calculated independently in this initial evaluation context. After the calculation is completed, CALCULATE starts to construct a new filter context.
    2. CALCULATE copies the original screening context to prepare a new screening context. In this process, the original row context is discarded because the new evaluation context will not contain any row context.
    3. CALCULATE performs a context switch. It uses the current value of the column in the context of the original row to provide a filter with a unique value for all columns being iterated. It is worth noting that this filter may or may not contain a single row, because the context switch does not guarantee that the new filter context contains only one row. If there is no active row context, skip this step. Once all implicit filters created by context switching are in the new filtering context, CALCULATE will proceed to step 4.
    4. CALCULATE calculates the regulator parameters USERELATIONSHIP, CROSSFILTER and ALL* (ALL type functions). This step occurs after step 3. This is very important and means that we can eliminate the impact of context switching by using ALL.
    5. CALCULATE evaluates all explicit filter parameters in the initial evaluation context, and applies the results to the new filter context generated after step 4. Once the context switch occurs, these filter parameters will be applied to the new filter context to overwrite the context generated by the conversion. This process occurs after step 4, that is, after the ALL* regulator removes the context and the model relationship structure is updated, so the context generated in this step will not be affected by ALL*. Colleagues, the calculation of the filter parameters occurs in the original context and is not affected by any other regulators or filters in the same CALCULATE function.
    6. Finally, CALCULATE evaluates the first parameter in the screening context generated in step 5.