DAX Advanced Functions

Advanced Functions within Microsoft Power BI.

 A. CALCULATE: 

TotalSales = CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2022) 

This example calculates the total sales amount from the Sales table for the year 2022. The CALCULATE function modifies the filter context by applying a filter to the Year column. 

B. FILTER: 

HighValueProducts = FILTER(Products, Products[Price] > 1000) 

This example filters the Products table to retrieve only the products with a price greater than 1000. The FILTER function selects the rows that satisfy the specified condition. 

C. ALL: 

TotalSalesAllYears = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Year])) 

This example calculates the total sales amount from the Sales table, removing any filters applied to the Year column. The ALL function removes the filters and considers all years for the calculation. 

D. RELATED: 

ProductCategory = RELATED(Categories[CategoryName]) 

This example retrieves the CategoryName value from the Categories table that is related to the current row in the data model. The RELATED function fetches values from a related table based on the defined relationships. 

These examples demonstrate how each advanced function in DAX can be used to manipulate the filter context, filter tables, remove filters, and retrieve values from related tables within Microsoft Power BI. 

E. Understanding and working with DAX table functions: 

• SUMMARIZE: Creates a summary table with aggregated values based on specified grouping columns.

SUMMARIZE(NewTable, Table1[Column1], Table2[Column2], "Total", SUM(Table1[Amount])) 

Creates a summary table "NewTable" with aggregated values. It groups the data by values in "Column1" from "Table1" and "Column2" from "Table2". It also includes a calculated column called "Total" that sums the "Amount" column from "Table1".

• CROSSJOIN: Combines two or more tables to generate all possible combinations of rows. 

CROSSJOIN(Table1, Table2)

Combines "Table1" and "Table2" to generate all possible combinations of rows between the two tables.

• VALUES: Retrieves distinct values from a column or table within the current filter context. 

VALUES(Table1[Column1])

Retrieves distinct values from the "Column1" column in "Table1" within the current filter context.
  

F. Using advanced aggregation functions: RANKX, TOPN, and PERCENTILEX: 

• RANKX: Ranks values based on a specified expression within a column or table. 

RANKX(Table1, Table1[Column1], , DESC)

Ranks values in "Column1" of "Table1" based on the values in the same column, in descending order.

• TOPN: Retrieves the top N rows based on a specified expression and sort order.

TOPN(5, Table1, Table1[Column1], ASC)

Retrieves the top 5 rows from "Table1" based on the values in "Column1", sorted in ascending order. 

• PERCENTILEX: Calculates the value at a specified percentile within a column or table.

PERCENTILEX.INC(Table1, Table1[Column1], 0.75)

Calculates the value at the 75th percentile within "Column1" of "Table1". 

G. Implementing time intelligence calculations with DAX: 

• TOTALYTD: Calculates the total year-to-date value based on a specified aggregation.

TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Calculates the total year-to-date value by summing the "Amount" column from the "Sales" table based on the "Date" column in the "Calendar" table. 

• SAMEPERIODLASTYEAR: Retrieves values from the same period in the previous year.    

​CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))

Retrieves values from the same period in the previous year based on the "Date" column in the "Calendar" table.

• DATESBETWEEN: Filters data based on a specified date range.

CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Calendar[Date], DATE(2022, 1, 1), DATE(2022, 12, 31)))

Filters data by the date range between January 1, 2022, and December 31, 2022, based on the "Date" column in the "Calendar" table.