topic Re: Power BI SWITCH in DAX Commands and Tips

Power BI SWITCH

ruxandraalina — Wed, 09 Mar 2022 08:15:10 GMT

Hello,

I have a turnover table where I calculate numerous turnover-related metrics inside a single measure using the switch() formula:

All Metrics =

var category = FIRSTNONBLANK('0_Categories'[Category], 1)

var metric = SWITCH(category,

"% Turnover", [% Turnover],

"% Turnover Voluntary", [% Turnover Voluntary],

"% Turnover Involuntary", [% Turnover Involuntary],

"% Turnover Other", [% Turnover Other])

return metric

The reason I am using the switch formula is because I wanted to create a matrix where all these metrics are on a row level (+months on the column level).

For further analysis, I also need to calculate turnover (=the All metrics measure) by country, language, etc.

Instead of creating 10 different matrixes (one for each field), I am wondering if I can use the switch function (or anything else) to create a slicer for these categories. Basically, if the user selects country, then he gets a matrix with turnover by country and by month; if the user selects language, then he gets turnover by language and by month, and so on.

Can anyone help me?

Thank you!

Re: Power BI SWITCH

amitchandak — Wed, 09 Mar 2022 08:46:31 GMT

ruxandraalina , Seem like you need axis slicer.

One is to add both of them on axis, As ask user to drill using parallel line

The second is enable personalization

Other are bookmark and unpivot

Axis Slicer
Three approaches from Guyinacube https://www.youtube.com/watch?v=dYmzb5UMkXw
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

Re: Power BI SWITCH

ruxandraalina — Wed, 09 Mar 2022 09:56:51 GMT

Thank you, amitchandak!

First approach from Guyinacube worked!