An interesting visualization pattern I have seen is that some customers want to be able to switch the axis on the chart dynamically using a slicer.
Let’s take a simple model like this:
where I want to be able to dynamically change the axis of my chart to be Currency, Country or Region using a slicer. Today that is not possible out of the box because you cannot have a single slicer crossing different table. Now one option here is to use bookmarks to switch the chart based on a label (but that is cheating 
) but I rather fix it in the model. What we need to do is bring all slicer values and their keys into a single table to be used in the slicer, for this use a DAX calculated table like this:
Table =
var currencyt = CROSSJOIN(ROW("Type","Currency"), VALUES(DimCurrency[CurrencyName]))
var country = CROSSJOIN(ROW("Type","Country"), VALUES(DimSalesTerritory[SalesTerritoryCountry]))
var region = CROSSJOIN(ROW("Type","Region"), VALUES(DimSalesTerritory[SalesTerritoryRegion]))
return UNION(UNION(currencyt,country), region)
First we use 3 variables to create 3 tables that each joins with the name we want on the slicer with the key values of that dimension. Finally we join all of them into a single table, i also rename the CurrencyName column to Values.
This gives us a single table:
Now we can create a slicer based on the type and create a chart based on the Key column
Now finally I write a measure that joins the names of the axis with the values on the dimension using TREATAS.
Measure 2 =
if(HASONEVALUE('Table'[Type]),
SWITCH(VALUES('Table'[Type])
,"Country", CALCULATE(SUM(FactInternetSales[SalesAmount])
,TREATAS(VALUES('Table'[Values])
,DimSalesTerritory[SalesTerritoryCountry]))
,"Currency",CALCULATE(SUM(FactInternetSales[SalesAmount])
,TREATAS(VALUES('Table'[Values])
,DimCurrency[CurrencyName]))
,"Region",CALCULATE(SUM(FactInternetSales[SalesAmount])
,TREATAS(VALUES('Table'[Values])
,DimSalesTerritory[SalesTerritoryRegion]))
)
)This calculation pushes the filtered down onto the dimension based on the slicer, it is using the filtered values from the values column into each column of the dimension. This is as if the filter was placed on the dimension instead of the table we just created.
This finally gives us the ability to slice on a dimension:
You can download the PBIX file here.
Hi Kasper, TREATAS seems to be a function not supported bij SSAS2016. Would there be another way to make the DAX work without using TREATAS ?
No TreatAs is really the key here.
What if I want to sort the countries on the graph by some specific order (probably not alphabetically)?
You can use the sort by column functionality to change the sorting order (not on the fly though)
My calculated sum is the same across every attribute, it doesn’t seem to be pushing the filter context to fact table.
Example:
“Cost Center”, CALCULATE(SUM(ftRevenue[ChargeAmount]), TREATAS(VALUES(dmAttributes2[Attribute]), ftRevenue[DeptGL]))
All of the cost centers have the same value. What am I doing wrong?
Forgot to mention that when I place the actual attribute column (DeptGL) into the visual, it correctly displays the amount for each attribute instead of the total amount.
I figured it out. The calculated measure has to be in the same table as the table of attributes for the filter context to be passed.
Hi Kasper, this is really cool. But I am unable to see the detailed record. “show records” by selecting visuals. it only shows summary results (measure values). Even unable to make drill through. It is mandatory requirement. Please suggest some solution.
Hi Kasper,
This is the one I was looking for. If i can choose top N values in the chart as per slicer selection on this solution. That would be really helpful.
Thanks,
Kul
Hi,
great tips. I had a problem doing this without DAX were I was not allowed to use calculated columns as slicer. This tutorial solved this issue. Thank you very much!
Hi Kasper,
I have tried this but need to have 14 different dimensions. This seems to slow down the report to almost unusable. Do you have any recommendations as to what to do in this instance? I was hoping someone had created a visual to allow variables dimensions? 🙂
Much appreciated
Hi Debbie, a real feature to allow this will come soon (I dont have a real timeline but hopefully later this year). You can see it in action here: https://www.youtube.com/watch?v=8WhXCwHynEE
Kasper
Hi Kasper – do you have any idea where in the video this is announced? The video is super long!
Thanks!
this should do it: https://youtu.be/8WhXCwHynEE?t=798
Hi Kasper,
Thank you very much for the article. Every article from Kasper On BI it has Your mark on.
Hi Kasper,
I am getting an “The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.” error on the measure calculation
Hi Kasper, I want to filter out the slicer based on the data in the Fact table. How can I filter the slicer which is based on Table above and show only the dimension Types that has data for a particular filter used in the report. For example, if Region returns no data for the filters used, I don’t want to show Region on the slicer.
Hello,
Very good solution. You can actually embed multiples dimension slicers into a measure slicer. This article is an extension of what is shown above:
https://www.godataviz.com/post/switching-dimensions-based-on-slicer-selection-without-bi-directional-filtering-disconnected-table