Data Analysis Expressions (DAX)

DAX is a language that thinks in tables, when starting with DAX, we begin by writing syntax for Calculated Columns, Tables and Measures- In simple terms, DAX is optimized for single-column operations.

PILLARS OF DAX

CALCULATED COLUMNS: Implicit

- Used in Axis, slicers, sorting, created in Query/Data View

- Represents single value per row, stored & attached to a specific table, consumes space.

CALCULATED MEASURES: Explicit

- Can vary aggregation, e.g. SUMX/ MINX/ COUNT per Syntax

- Represents single value per data model, not attached to any table, result is dynamic based on filter context

**Explicit filtering supersedes implicit filtering

CALCULTATED TABLES:

Best created in Power Query as the first TRANSFORMATION step or using DAX

KEY DAX CONCEPTS

- DAX always FILTERS Data first & then EVALUATE calculation

-EXPLICIT filters (Measures) OVERIDES IMPLICIT filtering (By Slicer/ Reports & Page Filters)

-When writing DAX: Avoid FILTERING entire TABLE when using ALL- Instead restrict filtering to MINIMUM COL

PEOPLE READ CODE & MACHINES EXECUTE IT

HENCE:

-FORMAT YOUR DAX CODE

-MAKE COMMENTS FOR FUTURE REFERENCE

DATES IN DAX

Every good model in Power BI starts with the calendar dimension.

Below are some useful functions to create dates table.

CALENDERAUTO FUNCTION

CREATES MIN and MAX range of ALL dates within MODEL.

=CALENDERAUTO ()

CALENDER FUNCTION

CREATE DATE TABLE with FIRST & LAST date in Selected COLUMNS

= CALENDAR(

MINX(Sale, Sale[Order Date]), //Picks up First Date

MAXX(Sale, Sale[Order Date])) //Picks up Last Date

CALENDER FUNCTION

CREATE DATE TABLE WITH SPECIFIC DATES

= CALENDAR("Start_Date", "End_Date")

CALCULATED COLUMNS (CAL-COL)

Calculated Columns: Typically done to describe existing data better.

Reason to Create Calculated Columns is Data categorization & Grouping

Example: Column for Sale Demographics, categorizing, breakdown.

Best Practice: Create columns prior to importing data into PBI for better compression & avoid Creating CAL-COL where MEASURES can be used.

Slicer: CAL-COL can be used to slice and dice your data

Relationship: CAL-COL are used to build relation between two tables

USEFUL COLUMN CONCATENATE SYNTAX

Combining Columns Basic= Region[City] & "- " & Region[Country]

Combining Columns DAX= COMBINEVALUES( ",", Table1[Column1], Table1[Column2])

CALCULATED COLUMNS: DATES

CAL COLUMN: START of Month (Groups all values on FIRST date of the month)

= DATE(YEAR('Date'[Date]), MONTH('Date'[Date]), 1)

CAL COLUMN: END of Month (Groups all values on LAST date of the month)

= EOMONTH('Date'[Date], 0)

CAL COLUMN: ORDER DATE KEY (Establish relationship using integer b/w Fact & Date)

=YEAR(Sales[Date]) * 10000+ MONTH(Sales[Date])*100 +DAY(Sales[Date])

CAL COLUMN: YearMonth KEY (Sort MonthYear in visuals with data from multiple year)

= YEAR('Date'[Date]) * 10000+ MONTH('Date'[Date])*100

CAL COLUMN: Weekend Weekday

=SWITCH(TRUE(), //Applies result where condition met

'Date'[Weekday#] = 1, "Weekday",

'Date'[Weekday#] = 2, "Weekday",

'Date'[Weekday#] = 3, "Weekday",

'Date'[Weekday#] = 4, "Weekday",

'Date'[Weekday#] = 5, "Weekday",

'Date'[Weekday#] = 6, "Weekend", //Saturday

'Date'[Weekday#] = 7, "Weekend", //Sunday

BLANK())

LOOK UP VALUE FUNCTION- FOR CALCULATED COLUMN- BETWEEN DIM (BASE TABLES) & FACT (EXPANDED TABLE)

RELATED > Gives access to LOOKUP VALUE from DIM TABLE (BASE TABLE / ONE SIDE OF RELATIONSHIP)

> Result is always a single Value

RELATED Example = RELATED(Region[City])

RELATEDTABLE > Gives access to LOOKUP VALUE from FACT TABLE (Expanded Table / Many Side)

> Result is a Table > Need to use ITERATORS (MINX/SUMX etc) to evaluate expression and return SCALER value.

= MAXX(

RELATEDTABLE(Sales), // Related Table to evaluate

Sales[Order Date]) //Iterate over column to run MAXX

MEASURES

Measures are dynamic aggregations and its considered best practice to build core measures then branch them out to other groups such as Cumulative, Average etc.

Commonly used DAX Functions

=SUM(«ColumnName»)

=AVERAGE(«ColumnName»)

=COUNT(«ColumnName»)

=DISTINCTCOUNT(«ColumnName»)

=DIVIDE(«Numerator»,«Denominator»,«AlternateResult»)

=MIN(<column>)

=MAX(<column>)

CORE MEASURES: Build your core measure table which branches out to other Measure table. See Branching Methodology Chart for a graphical presentation.

Total Sales = SUMX(Sales, Sales[Unit Sale] * Sales[Quantity])

Total Sale Less Disc= (Sales[Unit Sale] - Sales[discount]) * Sales[Quantity]

Total Cost = SUMX(Sales, Sales[Quantity] * Sales[Unit Cost])

Profit = [Total Sales] - [Total Cost]

Total Items = sum(Sales[Quantity])

Total Transactions = COUNTROWS(Sales)

Total Orders = DISTINCTCOUNT(Sales[Order ID])

Useful Time Comparison Measures

Sales LY = CALCULATE([TotalSale], DATEADD(Date[Date], -1, YEAR))

Sales LM = CALCULATE([TotalSale], DATEADD(Date[Date], -1, MONTH))

Sales LQ = CALCULATE([TotalSale], DATEADD(Date[Date], -1, QUARTER))

Sales Diff CY to LY $ = [TotalSale] - [Sales LY]

% Change Sales to LY = DIVIDE([Sales Diff to LY], [Sales LY], 0)

CALCULATE

Its one of the most powerful function in DAX which changes THE CONTEXT OF A CALCULATION

Calculate Automatically- FORCES promoting ROW CONTEXT TO CURRENT FILTER CONTEXT

Calculate modifies the filter context BEFORE evaluating

SYNTAX: CALCULATE( <expression>, <filter1>, <filter2>… )

> EXPRESSION is usually an aggregation/measure

> EXPRESSION is evaluated in a context that is modified by the filters

> EXPRESSION IS MANDATORY / FILTER IS OPTIONAL

> EXPRESSION can be a Measure or COLUMN Aggregation using SUM, COUNT, AVERAGE , COUNT, COUNTROWS, DISTINCTCOUNT, SUMX, COUNTX, AVERAGEX

> FILTER CANNOT be a MEASURE > IT MUST BE A COLUMN

> FILTER CAN be ALL/ALLEXCEPT/FILTER/DATEADD ETC

> FILTER RESULT HAS to be NUMBER/ TEXT/ DATE- It Cannot return a table

> FILTER can both enlarge and restrict the current context of evaluation

TIME INTELLIGENCE

🧿 DATEADD

Most Versatile Time Intelligence Functions in Power BI

Calculating Sales Previous Year

= CALCULATE([Total_Sales], DATEADD(Dates_Table[Date],-1,YEAR))

Calculating Sales Previous Month

= CALCULATE([Total_Sales], DATEADD('Dates_Table'[Date], -1, MONTH))

Calculating Sales Last Quarter

= CALCULATE([Total_Sales], DATEADD('Dates_Table'[Date], -1, QUARTER))

🧿 SAMEPERIODLASTYEAR

= CALCULATE([Total_Sales], SAMEPERIODLASTYEAR(Dates_Table[Date]))

🧿 DATESBETWEEN

Calculate sale BETWEEN a specified date range

=CALCULATE([TotalSales], DATESBETWEEN(DatesTable[Date], "01/01/2020", "15/01/2020"))

Calculate sales for last 30 days/ 30 Days trailing

= CALCULATE([TotalSales], DATESBETWEEN(DatesTable[Date], TODAY()-1, TODAY()-31))

🧿 TOTALYTD

Calculates running total to the latest date

= TOTALYTD([Total Sales], Dates_Table[Date])

= TOTALQTD([Total Sales], Dates_Table[Date])

= TOTALMTD([Total Sales], Dates_Table[Date])

CALCULATED TABLES

Great way to summarize the information into a single table

Example below to create a calculated table by using the summarize function & having customer as the group by field:

🧿 Summarized by CUSTOMER = // Calling values from Columns
SUMMARIZE('Table', 'Table'[Customer],
"Total Sales", SUM('Table'[Sales]),
"Total Profit", SUM('Table'[ProfitColumn]),
"Average Sale Amount", AVERAGE('Table'[Sales]),
"Highest Sales Amount", MAX('Table'[Sales]),
"First Sale Date", MIN('Table'[SalesDate]),
"Last Sale Date", MAX('Table'[SalesDate]))

🧿 Summarized by COUNTRY = // Calling values using Measures
SUMMARIZE(Region, Region[Country], //Summarize by Country
"Total Sales", [Total Sales], //Values to Display
"Total Transactions", [Total Transactions],
"Total Quantity", [Total Quantity],
"Total Profit", [Total Profit])

EXTRACT | TRANSFORM | VISUALIZE