Change your focus over time in DAX Measures

Author:Murphy | View: 29849 | Time: 2025-03-23 18:34:30

Introduction

How do you measure the success of an advertising campaign? Especially when you want to advertise different brands over time?

A similar question was posed to me by one of my clients.

He wants to analyze sales for his brands over time and compare them to those for advertised brands to recognize if the investment has paid off.

The challenge was to change the data model to make this analysis possible without breaking the existing reports and analysis.

Focus on selected Brands

The first step was to have a table of the advertised brands over time.

Something like this:

Figure 1 – Table with the focus brands per month (Figure by the Author)

In this table, you will find one or more brands for almost every month.

Now I can expand my data model.

The starting point in my Contoso data model is the following (extract from the entire data model):

Figure 2 – Initial Data model (Figure by the Author)

As you can see, I have a column for the Brand Name but no Brand ID.

In this case, I must create a Relationship through the column [BrandName].

Integrating the Focus Brands table

After adding the Focus Brands table to my Data model, I added the following Relationships:

Figure 3 – New Data model with the new Relationships (Figure by the Author)

Instead of bidirectional Filtering between the tables Focus Brands and Products, I set the Filtering "Focus Brand" filter "Product". But bidirectional filtering works as well, but it introduces one unneeded filter direction. We need to set the correct filter direction as we want to drill down the results with Attributes from the Product table.

As we have multiple Products within the table Product, with the same name, we must have a Many-to-Many (m:n) relationship to the Product table.

Another way to create a data model in this scenario is to add a Brands table containing one row for each Brand and create 1:n relationships from both tables to this new table.

For the sake of simplicity, I refrain from going this extra step here, and I stick with the m:n relationship. There are better solutions than this, as described in the SQLBI article referenced in the References section below.

The same applies to the relationship between the Focus Brands table and the Date table based on the MonthKey column.

But both Relationships must be Disabled to avoid ambiguity in the data model.

The critical relationship is between the Focus Brands and the Online Sales tables.

My first approach was without the relationship to the Online Sales table. But I had to use cross-filtering between the tables to calculate the results correctly.

This approach brought some issues:

Inability to calculate the part of sales for advertised and non-advertised products.
Inability to calculate time series correctly.
Most importantly, the report didn't work for all Users.

The last point was crucial. I used the CROSSFILTER() function in my Measures.

As you can read in the Remarks of the Microsoft documentation, CROSSFILTER() is not supported when Row-Level-Security is implemented in a data model, which my client had.

To implement the Relationship between the Focus Brands and the Online Sales tables, I had to add one new column in each table to map each Brand to each Month.

In the Focus Brands table, I added this column with the following expression:

Figure 4 – Focus Brands table with the Brand per Month column (Figure by the Author)

On the Online Sales table, I used this expression to add the corresponding column:

Brand per Month = RELATED('Product'[BrandName])
                  & "_" & CONVERT(RELATED('Date'[MonthKey]),STRING)

Now, I was able to add the new Relationship.

Create the Measure and check the Results

The Measure to calculate the Results is the following:

Online Sales for Focus Brands =
  CALCULATE([Sum Online Sales]
        ,USERELATIONSHIP('Online Sales'[Brand per Month]
                        ,'Focus Brands'[Brand per Month]
                        )
        ,USERELATIONSHIP('Focus Brands'[MonthKey]
                        ,'Date'[MonthKey]
                        )
      )

After adding a Visual with the [Sum Online Sales] and the new [Online Sales for Focus Brands] Measures, the result looks like this for March 2019:

Figure 5 – Check the result for the Focus Brands Measure (Figure by the Author)

As you can see, the results for both Measures are the same for each Focus Brand in March 2019 (marked in blue).

The new Measure is calculating the correct results for the Focus Brands.

But now, we must use the Brand Name column from the Focus Brands columns to get these results.

But the biggest issue is that I get a blank row for all Sales without a corresponding Focus Brand.

This is more apparent when using only the new Measure in a column visual:

Figure 6 – Focus Sales per month with Blank (Figure by the Author)

I can add a Filter to the Visual to exclude all rows with [BrandName] = Blank, but I would like to avoid this, as all users of the data model need to do it instead of solving this issue once with a good data model.

The target is to have the column Brand Name available for reporting only once without disrupting all other existing visuals or reports already in place using the Product table.

Improve the data model

To achieve this, I must ensure that the Filter context flows correctly when calculating the Sales for the Focus Brands.

Look at the following picture for the needed flow:

Figure 7 – The needed Filter flow directions (Figure by the Author)

In this case I must set bidirectional filtering between the tables Focus Brands and Product.

Then I must change my Measure to enforce the flow of the Filter context from the Product table through the Focus Brands table.

For this, I adapted the Measure to include a CROSSFILTER():

Online Sales for Focus Brands =
  CALCULATE([Sum Online Sales]
    ,USERELATIONSHIP('Online Sales'[Brand per Month]
                        ,'Focus Brands'[Brand per Month]
                    )
    ,USERELATIONSHIP('Focus Brands'[MonthKey]
                    ,'Date'[MonthKey]
            )
    ,CROSSFILTER('Online Sales'[ProductKey]
                ,'Product'[ProductKey]
                ,None
                )
    )

With this, we have the issue that this Measure will not work for all users to which an RLS Rule is applied.

Without this restriction, this Measure will work while using it with the Brand Name column from the Product table.

I tried other approaches to solve this issue, but whatever I did, it would either not work as needed or disrupt existing reports.

I even added a Brands table and intentionally set the relationship between the Brands and Focus Brands table to Many-to-Many. Still, it didn't solve the need for the CROSSFILTER() function:

Figure 8 – Data model with Brands table (Figure by the Author)

I set the relationship between the Focus Brands and the Brands table to Many-to-Many because the relationship turns to a restricted one, which avoids the creation of a row in the Result.

But it continued to work only with the CROSSFILTER() function in the Measure.

And the Drill Down still doesn't work.

At the moment I don't have a solution to this problem.

Conclusion

Initially, I thought the requirement would be relatively easy to solve.

It turned out to be a perfect example to explain how a good data model can solve problems and how to manipulate relationships to achieve a goal.

Even so, I couldn't remove the need for the additional filter.

Sometimes we must accept that the requirements collide and need a workaround to achieve our goals.

Based on the solution shown above, I can now create measures to calculate the success of an advertising campaign or how the sales of Focus Brands are related to all other brands.

References

Read this Article from SQLBI about the possible solutions when working with m:n Relationships: Different options to model many-to-many relationships in Power BI and Tabular – SQLBI

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The table Focus Brands was generated from this dataset with a semi-random process.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the Dax engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code: