Blog

You are currently viewing Advance DAX Concepts in PowerBI

Advance DAX Concepts in PowerBI

Loading

In this blog, you will learn about the Advance DAX Concepts in PowerBI such as Iterators, table function, relationship, advance time functions and many more.

Iterators

Iterators in Power BI are functions that perform operations row by row on a table. They are essential for calculations that can’t be done with simple column aggregations. Let’s go through the steps to use some common iterator functions: SUMX, AVERAGEX, MINX, and MAXX. Dataset used in this experiment is given here.

Step-by-Step Example (Using SUMX):

Load Data:

  • Open Power BI Desktop.
  • Click on Home > Get Data > CSV (or any other source you are using).
  • Load a sample sales dataset with columns like Product, Quantity, and Price.

Create a Calculated Column:

  • Go to Modeling > New Column.
  • Name the column TotalSales.

Use the following DAX formula: 

TotalSales = Sales[Quantity] * Sales[Price]

Use SUMX to Aggregate:

  • Go to Modeling > New Measure.
  • Name the measure TotalSalesByProduct.

Use the following DAX formula:

TotalSalesByProduct = SUMX(Sales, Sales[Quantity] * Sales[Price])

Visualize the Data:

  • Drag the Product field to the Axis of a column chart.
  • Drag the TotalSalesByProduct measure to the Values field.
  • This chart now shows the total sales for each product using the SUMX iterator function.

Exploring Other Iterators

AVERAGEX: To calculate the average sales: 

AverageSalesByProduct = AVERAGEX(Sales, Sales[Quantity] * Sales[Price])

MINX: To find the minimum sales value: 

MinSalesByProduct = MINX(Sales, Sales[Quantity] * Sales[Price])

MAXX: To find the maximum sales value: 

MaxSalesByProduct = MAXX(Sales, Sales[Quantity] * Sales[Price])

These iterator functions are powerful for performing row-by-row calculations and aggregating the results in meaningful ways.

Table Functions

Table functions return tables rather than single values. They are essential for defining calculated tables, creating complex filter contexts, and performing advanced data shaping.

Step-by-Step Example (Using FILTER and ALL):

Create a Calculated Table with FILTER:

  • Go to Modeling > New Table.
  • Name the table HighSales.

Use the following DAX formula to filter rows where sales are high: 

HighSales = FILTER(Sales, Sales[Quantity] > 10)

Using ALL to Ignore Filters:

Create another measure to calculate total sales ignoring any filters: 

TotalSalesIgnoringFilters = CALCULATE(SUM(Sales[TotalSales]), ALL(Sales))

Visualize the Data:

  • Create a table visual with fields from the HighSales table.
  • Add the TotalSalesIgnoringFilters measure to any visual to see its effect.

Exploring Other Table Functions

VALUES: Returns a one-column table with distinct values from the specified column. 

UniqueProducts = VALUES(Sales[Product])

ADDCOLUMNS: Add calculated columns to a table. 

SalesWithDiscount = ADDCOLUMNS(Sales, "DiscountedPrice", Sales[Price] * 0.9)

SUMMARIZE: Creates a summary table. 

ProductSummary = SUMMARIZE(Sales, Sales[Product], "Total Sales", SUM(Sales[TotalSales]))

CROSSJOIN: Returns a Cartesian product of all rows from all tables specified. 

CrossJoinExample = CROSSJOIN(Table1, Table2)

CALCULATETABLE: Evaluates a table expression in a context modified by filters. 

SalesForHighQuantity = CALCULATETABLE(Sales, Sales[Quantity] > 10)

Relationships and DAX

Relationships define how tables connect to each other, crucial for accurate data analysis. Here’s how to manage relationships and use DAX functions to leverage these relationships.

Step-by-Step Example (Creating and Using Relationships):

Load Data:

  • Load a Sales table and a Products table. (ProductSales.csv and Products.csv)
  • Ensure both tables have a common column, like ProductID.

Create Relationships:

  • Go to the Model view.
  • Drag and drop ProductID from the Sales table to ProductID in the Products table to create a relationship.
  • Configure the relationship as one-to-many if needed.

Using RELATEDTABLE:

  • Create a new table to fetch all related sales for each product:
ProductTotalSale = ADDCOLUMNS(Products, "RelatedSales", COUNTROWS(RELATEDTABLE(ProductSales)))

Using USERELATIONSHIP:

  • If you have multiple relationships, specify which to use:
CustomRelationshipMeasure = CALCULATE(SUM(Sales[TotalSales]), USERELATIONSHIP(Sales[ProductID], Products[AlternateProductID]))

Advanced Time Intelligence

Time intelligence functions allow you to create sophisticated date and time-based calculations. These are crucial for performing trend analysis, forecasting, and period comparisons.

Step-by-Step Example (Using Time Intelligence Functions):

Create a Date Table:

  • Go to Modeling > New Table.
  • Use DAX to create a date table:
DateTable = CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate]))

Mark as Date Table:

  • Go to Model View.
  • Right-click the Datetable and choose Mark as Date Table.

Year-over-year calculation:

  • Create a measure to calculate year-over-year sales:
SalesYTD = CALCULATE(SUM(Sales[TotalSales]), DATESYTD(DateTable[Date]))

Comparison to previous year:

  • Create a measure to calculate sales for the same period last year:
SalesPreviousYear = CALCULATE(SUM(Sales[TotalSales]), SAMEPERIODLASTYEAR(DateTable[Date]))

Moving average calculation:

  • Create a measure for a 3-month moving average:
MovingAverage3Months = AVERAGEX(DATESINPERIOD(DateTable[Date], LASTDATE(DateTable[Date]), -3, MONTH), [TotalSales])

Visualize time intelligence:

  • Create a line chart.
  • Add DateTable[Date] to the axis.
  • Add TotalSales, SalesYTD, and SalesPreviousYear to the values.

Conclusion

Power BI’s advanced features, such as iterators, table functions, relationships, and time intelligence, enable you to build powerful and insightful data models. By mastering these tools, you can perform sophisticated data analysis and visualizations, providing deeper insights into your data.

If you like the article and would like to support me, make sure to: