Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Got a good question this week that had me scratching my head for a bit but then I remembered a new function that was added to Power BI (and SSAS) recently called TreatAS. Marco covered it in detail here. So what they wanted to do is have a visual where they can view the sales and compare it with sales of different colors. So let’s get too it.

Too start out please make sure you read Marco’s post really well, the trick I am about to show you works really well but if you can use the alternative (real relationships) it is preferred for performance reasons. Having said that lets continue.

I have a very simple model with Sales by product:

Now I visualize it by creating a visual that shows Sales by Manufacturer:

The goal here to show 4 bars:

• one with the total sales
• one with sales only for specific colors selected by a slicers
• one with sales only for specific colors selected by another slicers
• one with the remaining sales not part the selection

To start I want to populate and create the slicers. I can’t use the values of the table itself as that would filter all the results so I have to create two new tables with these values to make “disconnected” slicers. To do this I create a two new calculated tables

Colors = VALUES(DimProduct[ColorName])

and

MoreColors = VALUES(Colors[ColorName])

This created two new tables in my model with just the colors:

Observe I didn’t create any relationships as I want to control this in the measure itself (more on this later)

Next I add the values as slicers to the report:

So now I want to add the bars for sales of all colors selected in Color 1 and another for all colors in Color 2. To do this I add a new measure using the new TREATAS function:

CALCULATE(SUM(FactOnlineSales[SalesAmount]),TREATAS(VALUES(Colors[ColorName]),DimProduct[ColorName]))

What this measure does is calculate the Sum of SalesAmount and filtering the ColorName from the DimProduct table with the values if the current selected values of ColorName from the Colors table, like an actual relationship was used.

Now going back to the visual where I added the measure and selected 2 colors, we only see the sales for those 2 colors selected:

Now adding the same measure for Color 2

MoreColorsSelected = CALCULATE(SUM(FactOnlineSales[SalesAmount]),USERELATIONSHIP(MoreColors[ColorName],DimProduct[ColorName]))

As last measure I am adding the remaining sales:

RemainingColors = SUM(FactOnlineSales[SalesAmount])-[ColorsSelected]-[MoreColorsSelected]

Now that I have that I can also add this using stacked charts to create a single bar that is split up dynamically:

Now this works great but I hope you read Marco’s blog post and read his warning, whenever you can you should always use relationships. While I was working on this blog post it dawned on me that we can just as well use Inactive relationships here. So I went to the diagram view and created them:

Then instead of using TREATAS I am using our traditional USERELATIONSHIP instead

MoreColorsSelected = //CALCULATE(SUM(FactOnlineSales[SalesAmount]),TREATAS(VALUES(MoreColors[ColorName]),DimProduct[ColorName]))
CALCULATE(SUM(FactOnlineSales[SalesAmount]),USERELATIONSHIP(MoreColors[ColorName],DimProduct[ColorName]))

this will, only for this measure, activate the relationship and filter the product table with the selects colors. This gives the same results but with better performance, now with a small dataset like this you will never notice any issues but if you use this with billions of rows and complex calculations any performance gain will help. It still shows you that you can use TREATAS in other more interesting scenario’s or example lets say we want to see the sum of sales amount for both selections at the same time. I could write something like this:

MoreColorsSelected2 = var Selections = UNION(VALUES(Colors[ColorName]),VALUES(MoreColors[ColorName]))
return CALCULATE(SUM(FactOnlineSales[SalesAmount]),TREATAS(Selections ,DimProduct[ColorName]))

This will use the UNION of these two values as filter for the ColorName column, now we can extend this to do all kind of cool things here but I will leave that up to your imagination

You can download the entire file here: https://github.com/Kjonge/DemoWorkbooks/blob/master/selection.pbix