Data lineage in DAX

[Dangar332]

In this short blog post, I want to share a recent experience where I faced an issue. It’s not exactly an issue but rather a concept of lineage in Power BI.

I’m working on a report where I need to calculate the percentage of sales by month within a year. The month is represented by its name (Jan, Feb, Mar, etc.). The general approach is to find the total sales amount for the whole year and then divide it by the sales amount for each particular month.

I have created a similar scenario using the Contoso data model.

Here is the data model of Contoso:

Let’s create measures and put them in Matrix visuals to get a better understanding:

Here, we can see three measures: “Sales Amount,” “Year-Based Sales Amount,” and “Month-Wise %” which work fine as expected.

Below is the code for the measures used in the Matrix:

Sales Amount =                       ---- First Measure
SUM(Sales[SalesAmount])

Year-Based Sales Amount =            ---- Second Measure
CALCULATE(SUM(Sales[SalesAmount]), ALL('Calendar'[MonthName]))

Month-Wise % =                       ---- Third Measure
DIVIDE([Sales Amount], [Year-Based Sales Amount])

However, I need the month names in order (like Jan, Feb, Mar, Apr, etc.). We have a Calendar table where we have a "MonthOfYear" column in numeric form, so we can use it to sort the "MonthName" column.

After sorting the MonthName column based on MonthOfYear, in the Matrix, we can see our MonthName column is sorted Month-Wise.

But, as we sort the MonthName column based on MonthOfYear, we can see both Sales Amount and Year-Based Sales Amount have the same value. This causes our Year-Based Sales Amount measure to break, resulting in the Month-Wise % showing 100% for all months and the year.

At first glance, it’s not apparent why this happens because we usually do the sorting before developing the report and writing measures.

The reason this happens is due to lineage. When we sort the MonthName column based on the MonthOfYear column, the MonthName column preserves the lineage of the MonthOfYear column.

Now, consider the code of the “Year-Based Sales Amount” measure. We remove the filter context of the MonthName column, which is correct. However, when we sort the MonthName column, it preserves the MonthOfYear column’s lineage, so the filter context of that column (MonthOfYear) still applies. Therefore, we also need to remove the filter context of the MonthOfYear column.

Let’s amend the measure to remove the MonthOfYear column’s filter context:

Year-Based Sales Amount = 
CALCULATE(
  SUM(Sales[SalesAmount]), 
  ALL('Calendar'[MonthName], 'Calendar'[MonthOfYear])
)

Now we can see the Month-Wise % is correct.

This issue is due to the lineage of the MonthOfYear column. Because of this lineage, the filter context of the MonthOfYear column also applies to the MonthName column.

There are other ways to sort the MonthName , such as writing a ranking measure and using it in the Matrix to sort by the ranking measure. However, if you use the approach mentioned above, you need to understand the concept of lineage.

Remark: If you remove the filter context from the entire table, you won’t have to worry about lineage. However, this is not a good practice.

From the image below, you can see why it’s not advisable to remove the filter context from the whole table. Doing so also removes the filter from the Year column, which is undesirable.