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

• 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

• 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}

• ​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”))

Multi-lines DAX formula​