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:

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:

Comments

3 ways to filter SharePoint lists and libraries

One of the greatest benefits of metadata is the ability to filter on it and get to what you want with just a few clicks. Filtering for information is quite a common exercise these days. Every time we do online shopping, we filter for stuff. When we shop on Amazon® for shoes (something I do every day 🙂 ), we filter on cost, shoe size, reviews, brand among other things. When you go to a bookstore website, you filter for author and genre. When you research your dream car – you filter on car type, fuel economy, color. In SharePoint, we have not one, but three ways to filter SharePoint metadata! Today I would like to describe and explain all the three options to you. Option 1: Column header filter This is the most basic option that has been available ever since custom columns became available in SharePoint. Essentially this is the filtering you do using column headers. The functionality might be well familiar to many users as this is something we can do in Excel as well. ...

PowerApps

PowerApps is part of Office 365 and allows users to create business applications a couple of hours. This article shows you how to start learning the basics of PowerApps. Where do I develop a PowerApp? PowerApps development is done in PowerApps Studio, which is a downloadable client application or directly inside your browser. Start with a template … My recommendation, if you’re just starting, is to create your first PowerApp from one of the templates included in the product. At the moment do not worry about how the PowerApp looks like. Just click on the “create” button and pick a template to use in your business environment. Branding and building for mobile devices Some applications in the templates section have multiple design modes. In the example of the Estimator Pro PowerApp, there are two design modes; the phone factor and tablet factor. A phone factor does not mean your PowerApp cannot be displayed on a PC or tablet; it means it will work ...

Five Office 365 Security Improvements You Can Make Right Now

When it comes to cloud services, many organizations seem to forget their basic rules and responsibilities surrounding security. Some tend to think because Microsoft hosts Office 365, for example, that Microsoft will handle security for them. Unfortunately, they couldn’t be more wrong. Microsoft will protect your data from those who attempt to gain access to its systems, as well as offer protection from data loss due to an outage or failure of their infrastructure. However, if your credentials were to become compromised, you’re on your own as far as Microsoft is concerned. During the on-premises era, we were much more risk-conscious, and most companies had robust security policies in place. But now that data has become accessible from all over the world, it is crucial to be aware of the increase in risk and implement additional security measures to cover it. In this article, we will share how you can improve your security within Office 365 without breaking the bank....

Aziz's SharePoint

Search This Blog