Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

SharePoint Framework (SPFx).

What is SharePoint Framework   Client Side SharePoint Development using SharePoint Framework (SPFx). The SharePoint Framework (SPFx) development is a great milestone for SharePoint Client Side development. It is a page and web part model. This provides full support for client-side SharePoint development. It allows to easily integrate component with SharePoint data and supports for open source tooling. With SharePoint Framework, you can use any of your preferred web technologies and tools in your preferred development environment. It is to build responsive and mobile friendly SharePoint products and apps. SharePoint Framework (SPFx)   helps to build custom user interface extensions on top of SharePoint Online in supportive and future prepared way as SharePoint Online keeps itself updated time to time. Create new web part project You need to perform following steps:   1.       Create new project directory at your opted loca...
Post a Comment
Read more

Plan SharePoint Migration

Guide to a SharePoint Migration This blog will show how enterprises can approach their SharePoint migration with a few tried and tested action plans. This blog also details how you can successfully define a migration  roadmap, strategy, and goals for a successful SharePoint migration. There are four big transitions you’ll have to consider, before you dive into how to set up a pre-migration strategy. The Different Paths to Modern Management The following explains the four transitions and migrations that can be executed—successful or not. Cloud-first The Cloud-first path is perfect for startups, as there is no need for a migration. Essentially, everything is created in the cloud. Think: Cloud first and only cloud. Big Switch Transition The Big Switch path is often considered the most risky strategy, as organizations can never verify if the transition (also known as “big bang transition”) will end up as a success or a failure. Group by Group Transiti...
Post a Comment
Read more

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 ...
Post a Comment
Read more