Dynamic Columns Based on Slicer Selection in Power BI

*TO ACCOUNT FOR TOTALS FOLLOW THESE STEPS*
1) Edit your Avg Tempo Dimension Slicer Measure
2) Set your original switch statement to the variable N
3) Create a new variable called T to take care of the scenario in which SELECTEDVALUE() is BLANK() (AKA when DAX is trying to calculate the total)
4) Use ALLSELECTED() in the Spotify table to mimic DAX calculating the total in the table or matrix
VAR T =
SWITCH (
TRUE (),
SELECTEDVALUE ('Dimension Slicer'[Dimension] ) = "Album", CALCULATE ( [Avg Tempo], ALLSELECTED ( Spotify[Album] ) ),
SELECTEDVALUE ( 'Dimension Slicer'[Dimension]) = "Key Name", CALCULATE ( [Avg Tempo], ALLSELECTED ( Spotify[key_name] ) )
)
5) RETURN
IF ( ISBLANK ( SV ), T, N )

Learn how to change the column values in your Power BI visuals based on a slicer selection. One of my favorite DAX solutions using a calculated table and a measure. Can be useful to create different report views (switching column values in a single visual).

Also, welcome to my first OFFICIAL Power BI tutorial :D

► Music Credit: LAKEY INSPIRED
Track Name: "Blue Boi"
License for commercial use: Creative Commons Attribution 3.0 Unported "Share Alike" (CC BY-SA 3.0) License.

[jennytumay]

guys!! as much as it pains me to say that microsoft power bi has outdone me, microsoft power bi has outdone me, with a new preview feature called field parameters. It's basically what i'm doing here but in a less "hacky" way since it's giving power bi the innate ability to do dynamic columns with slicer selection. go check it out :)

[BackPly]

Thanks this has guided for my excel power pivot solution

Dimenison Bin Slicer:=VAR SV = IF(HASONEVALUE(Table__DimensionBin[Month]),

RETURN SWITCH(
TRUE(),
IF(HASONEVALUE(Table__DimensionBin[Bin]), = "Month",
CALCULATE([Measure Selection], FILTER(dim_Date, dim_Date[Month] = SV)),

IF(HASONEVALUE(Table__DimensionBin[Bin]), = "Week of Year",
CALCULATE([Measure Selection], FILTER(dim_Date, dim_Date[Week of Year] = SV)),

IF(HASONEVALUE(Table__DimensionBin[Bin]), = "Quarter",
CALCULATE([Measure Selection], FILTER(dim_Date, dim_Date[Quarter] = SV))
)

[lukass]

Hi, I have got 10 years of BI experience developing mostly in Tableau, Qlik and Spotfire, now learning Power BI. I am quite positive I would not be able to so clearly and smoothly transfer my knowledge to someone else. I am really impressed and intimidated I have to compete with such an impressive developer :) ! Also, really enjoy seeing your sample project choices... Taylor Swift prequeled by Star Wars... :D Way to go, just please dont get way too good ! (or at least be expensive so there is some job left for the elderly) :D

[shwetasrivastava]

Thanks for sharing this Jen.. I struggled with this some time back.. with this vid up others will be spared the ordeal ..🤗🤗😘

[nicky_rads]

Awesome! Good demo of some advanced Dax and how to implement into a report, keep it up !

[smsohelozzaman]

I liked your comment on SWITCH that it is a glorified IF statement. On the other day, I was telling the same to one of my colleague and he was angry with me ;). He thought he was coding by just making one switch statement in Power BI.

[dandylion]

Thanks for this video, exactly what I needed!

[stefankleinhans]

Hi,
Is it possible to have a 1 slicer change the Matrix Table rows, columns and value respectively
for example
Slicer has two selections A and B
When I select A on the slicer the Matrix table Row shows a list of cars and the purchase Cost in the values and the 5 countries in the columns header
When I select B on the slicer the Matrix table Row shows the 5 counties and the list of cars in the column header and the selling price in the value

Is this possible and how do I do it

[shivangdubey]

Hi Jenny, Please help me with this calculation what's wrong with the below calculation,

Count Dimension Slicer =
VAR SV = SELECTEDVALUE('Dimension Slicer'[Entity])
RETURN SWITCH(TRUE(),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Business Unit", CALCULATE(COUNT(Data[Business Unit]), FILTER(Data, Data[Business Unit] = SV)),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Database", CALCULATE(COUNT(Data[Database]), FILTER(Data, Data[Database] = SV)),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Therapeutic Area", CALCULATE(COUNT(Data[Therapeutic area]), FILTER(Data, Data[Therapeutic area] = SV)),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Statistician", CALCULATE(COUNT(Data[Statistician]), FILTER(Data, Data[Statistician] = SV)),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Study Lead", CALCULATE(COUNT(Data[Study Lead]), FILTER(Data, Data[Study Lead] = SV)),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Status", CALCULATE(COUNT(Data[Status]), FILTER(Data, Data[Status] = SV)),
SELECTEDVALUE('Dimension Slicer'[Dimension] = "Quarter", CALCULATE(COUNT(Data[Quarter Started]), FILTER(Data, Data[Quarter Started] =

[snoozy]

Dynamic Axis names! Will pass it along :)

[stephanietumay]

Wow this stuff is so interesting. Cool 👍👍

[faridaakbarli]

Thanks Jenny! Can you also give google drive link for this powerbi so that we can download and play with it?

[sajadahmadi]

Thank you very much, it was very helpful <3

[shivangdubey]

This is the error I am facing

Too many arguments were passed to the selected value function. The maximum argument count for the function is 2

[pearlking]

This works well for other visuals but in matrix it doesn't generating the row wise total....is there any other way around to solve the issue?

[TocaDefne]

Thanks for this great Job.I stucked with this.When we use table or matrix table instead of barchart, how can we get column total at the bottom?

[TocaDefne]

Hey Jenny! Where r u?We’r waiting new surprising videos.

[dharanidhar]

Hi, Can we do something in a table visual?