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.

Let’s block ads! (Why?)

Kasper On BI