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.

18 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 ?

    1. You can use the sort by column functionality to change the sorting order (not on the fly though)

  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.

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

  5. 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!

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

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

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.