Dynamically switching axis on visuals with Power BI

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:

image

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 Smile)  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:

image

Now we can create a slicer based on the type and create a chart based on the Key column

image

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:

image

image

You can download the PBIX file here.

8 Replies to “Dynamically switching axis on visuals with Power BI

  1. 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 ?

  2. 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?

    1. 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.

      1. 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.

  3. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *