Learn DAX Basics

Learn DAX Basics

by Li Da Gan

​Learn DAX Basics

• ​Data ​Analysis Expressions (DAX)

• ​A collection of functions, operators and constants that can be used in a formula and expression

• ​Calculate and return one or more values

• ​Provide capabilities to solve data modeling and analytical problems

• ​3 fundamental concepts – Syntax, Functions and Context

• ​DAX in Power BI

Fill in the Blank

DAX stands for ___

Syntax​

• Calculated Column​

• ​E.g. Margin = FactSales[SalesAmount] – FactSales[TotalCost]

• In the FactSales table, for each row in the Margin calculated column, calculate (=) a value by subtracting (-) values in the [TotalCost] column from values in the [SalesAmount] column

• ​

• Calculated Measure

• E.g. ​Sum of Sales Amount = SUM(FactSales[SalesAmount])

• For the measure named Sum of Sales Amount, calculate (=) the SUM of values in the [SalesAmount] column in the FactSales table

Multiple Choice

What always surrounds a column name in a DAX formula?

• Ampersand &

• Brackets [ ]

• Parentheses ( )

• Exclamation !

Fill in the Blank

How would you write a formula for the following:

In the DimProduct table, for each row in the UnitMargin calculated column, calculate a value by subtracting values in the UnitCost column from values in the UnitPrice column?

​Functions

• Predefined formulas that perform calculations by using specific arguments, in a particular order or structure

• Categories: Date and Time, Information, Logical, Mathematical, Statistical, Text, and Time Intelligence Functions

• DAX functions are unique in the following way:

• _{​Always references a complete column or a table. Use filters to the formula if use only particular values from a table or column}

• ​_{Calculations vary by context}

• ​_{Many functions return a table rather than a value. The table is not displayed, but is used to provide input to other function}

Multiple Select

What does a function always reference?

• Variables

• Arguments

• Table

• Column

Multiple Choice

What function would you use to concatenate two text strings into one string?

• CONCATENATE( ) 

• VALUE( )

• CONCATENATEX( )

• EXACT( )

Multiple Choice

Can a formula contain more than one function?

• YES

• NO

​Context

• ​Row Context

• ​The current row

• M​argin = FactSales[SalesAmount] – FactSales[TotalCost]

• ​

• ​Filter Context

• ​One or more filters applied in a calculation that determines a result or value

• ​Define a filter using functions such as ALL, RELATED, FILTER, CALCULATE

• ​SumofSalesAmount = SUM(FactSales[SalesAmount])

• ​StoreSales = CALCULATE(SUM(DimChannel[Sales]), FILTER(DimChannel, DimChannel[ChannelName]=“Store”))

Multiple Select

What are the two types of context?

• Row

• Column

• Filter

• Group

Fill in the Blank

Filter Context : One or more _ in a calculation that determines a single value

Multiple Choice

Which part is the context?

StoreSales = CALCULATE(SUM(DimChannel[Sales]), FILTER(DimChannel, DimChannel[ChannelName]=“Store”))

• StoreSales

• Sum(DimChannel[Sales])

• FILTER(DimChannel, DimChannel[ChannelName]=“Store”)

Advanced Techniques

Multi-lines DAX formula​