Tag Archives: Slicers

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:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Now adding the same measure for Color 2

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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  Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

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

Let’s block ads! (Why?)

Kasper On BI

DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

Power BI Report thumb DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

Guess how many articles are here on PowerPivotPro.com?  Go ahead and think of a number, I’ll wait.

The answer, at time of writing, is 923.  Rob alone has published 715 articles!  And these date all the way back to 2009.

A lot of these articles are “old,” but folks, the DAX engine is still 99% the same today in Power BI (and Excel 2016) as it was when it first “hit the shelves” in Spring 2010.

The motivation behind this “Reanimator” series, then, is twofold:

  1. Help newer converts/readers rediscover some of the most-awesome techniques previously covered here (without being so lazy as re-posting them in their original form)
  2. “Refresh” those techniques for the brave new world of Power BI (since the vast majority of old articles were written when we only had Power Pivot)

What better way to do that than to re-create those workbooks in Power BI Desktop and embed the report directly…Within. This. Post! wlEmoticon smile DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

A New Age of Self-Service BI Users

I’ve been fortunate enough to be given the honor of sharing with you, our community, all these wonderful posts written by many of our in-house industry experts. Updated in all their glory into the wonderful world of Power BI. Now you can click, slice, interact, touch (…dirty), and drill (dirtier!) with these reports to your hearts desire. Just as the BI gods intended them to be! My hope is that these updates will instill these tools to the growing number self-service BI users just getting into the field and who want to do AWESOME things with their reports.

Highlights From The Original Post(s)

So this update is actually a continuation of not just one…but TWO posts written by Rob in the distance past of 2012 (in technology years that’s basically forever). The two original posts were:

Dynamic TopN Reports Using PowerPivot V2!

Dynamic TopN Reports via Slicers, Part 2

Excel Report thumb DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

Rob demos some pretty ingenious techniques using his (now prolific) disconnected slicers technique to not only control the Top N Number you’d like to see on charts or graphs, but also the Value that you want to see that Top N Number ranked on. I’ve used it in MANY reports I’ve made over the years, always impressing the customers who used them.

Now I don’t want to give too much away in this post, instead directing you back to the walkthrough via the links above. I’m just here to whet your appetite enough with some fancy Power BI Reports, and if you want to learn the DAX code, hop into Rob’s posts.

This “Picture” Below is an Interactive Power BI!

Isn’t Something Missing?

Some of our more avid blog readers may be thinking “wasn’t there a THIRD post about TopN filtering?”. Yes, in fact there was. It was written by guest contributor Colin Banfield and is called Dynamic TopN Reports via Slicers, Part 3. It’s a fantastic post which covers ways to add BottomN metrics, Month/Year slicers, and more. I chose not to use that workbook since I wanted to capture the core story from the original posts written by Rob. If you’re inclined however, I recommend reading all three as they will add real value to your DAX tool belt. Until next time P3 Nation!

Download the Files!

Download the PBIX files

X

Get Your Files

Let’s block ads! (Why?)

PowerPivotPro