Dynamic Columns Based on Slicer Selection in Power BI

Рет қаралды 42,349

Күн бұрын

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"
Music By: LAKEY INSPIRED @ / lakeyinspired
Original upload HERE - www.youtube.com/watch?v=wAukv...
Official "LAKEY INSPIRED" KZfaq Channel HERE - kzfaq.info/love/Omy...
License for commercial use: Creative Commons Attribution 3.0 Unported "Share Alike" (CC BY-SA 3.0) License.
Full License HERE - creativecommons.org/licenses/...
Music promoted by NCM goo.gl/fh3rEJ

Пікірлер: 47

@jennytumay 2 жыл бұрын

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 :)

@aashiloh 2 жыл бұрын

amazing !!! kzfaq.info/get/bejne/ituWodZy3bDXmqc.html

@ririb6502 Жыл бұрын

Hi Jenny. I just want you to know that this version saved me from redoing my report. The version of some pbi (reporting server version) does not have the parameter. I want to thank you for this Just created this account to comment and subscribe to you! xoxo

@jennytumay Жыл бұрын

@@ririb6502 omg yay!! thank you riri im so glad 🙏🥰

@TheImac1616 Жыл бұрын

Can you make a new video?

@jennytumay Жыл бұрын

@@TheImac1616 do you have any requests?!!!

@Back1Ply 28 күн бұрын

Thanks this has guided for my excel power pivot solution Dimenison Bin Slicer:=VAR SV = IF(HASONEVALUE(Table__DimensionBin[Month]), VALUES(Table__DimensionBin[Month])) RETURN SWITCH( TRUE(), IF(HASONEVALUE(Table__DimensionBin[Bin]), VALUES(Table__DimensionBin[Bin])) = "Month", CALCULATE([Measure Selection], FILTER(dim_Date, dim_Date[Month] = SV)), IF(HASONEVALUE(Table__DimensionBin[Bin]), VALUES(Table__DimensionBin[Bin])) = "Week of Year", CALCULATE([Measure Selection], FILTER(dim_Date, dim_Date[Week of Year] = SV)), IF(HASONEVALUE(Table__DimensionBin[Bin]), VALUES(Table__DimensionBin[Bin])) = "Quarter", CALCULATE([Measure Selection], FILTER(dim_Date, dim_Date[Quarter] = SV)) )

@nicky_rads 2 жыл бұрын

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

@jennytumay 2 жыл бұрын

thank you nick :)

@dandylion16 2 жыл бұрын

Thanks for this video, exactly what I needed!

@shwetasrivastava2416 2 жыл бұрын

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

@jennytumay 2 жыл бұрын

Yay shweta im glad 😊 literally same until i figured it out i was stumped for a while 😅

@stephanietumay5650 2 жыл бұрын

Wow this stuff is so interesting. Cool 👍👍

@indzara 2 жыл бұрын

Thanks for sharing. Very effective.

@jennytumay 2 жыл бұрын

Of course! Love to hear that!

@lukass1157 2 жыл бұрын

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

@jennytumay 2 жыл бұрын

🥺🥺 thank you for the kind comments seriously so sweet and stoked u enjoyed those vids!!

@sajadahmadi595 2 жыл бұрын

Thank you very much, it was very helpful

@jennytumay 2 жыл бұрын

yay!! you're very welcome 🤩

@fabsrox1837 2 жыл бұрын

thank you so much for the tutorial

@jennytumay 2 жыл бұрын

you're welcome :)

@snoozy76 2 жыл бұрын

Dynamic Axis names! Will pass it along :)

@ERPCoder 2 жыл бұрын

Great!

@faridaakbarli Жыл бұрын

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

@brando2818 2 жыл бұрын

Solid.

@smsohelozzaman3026 2 жыл бұрын

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.

@jennytumay 2 жыл бұрын

😂

@shivangdubey2494 2 жыл бұрын

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] = SV))))))))))

@stefankleinhans4895 6 ай бұрын

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

@TocaDefne4 2 жыл бұрын

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?

@jennytumay 2 жыл бұрын

Omg! Okay try doing this: 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 ) Hope this helps! I tested it out and it was working on my end :)

@TocaDefne4 2 жыл бұрын

@@jennytumay Great !!!. Yes it is working. Appreciated

@bretthopkins7598 2 жыл бұрын

@@jennytumay Had the same question. Great solution.

@jennytumay 2 жыл бұрын

@@bretthopkins7598 🤩

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

@jennytumay Жыл бұрын

Hi muthu, i added steps for calculating the total in the description box!

@pearlking Жыл бұрын

@@jennytumay Thanks for your swift response. I'll try to work those steps & let you know.

@pearlking Жыл бұрын

@@jennytumay Thank you very much Jenny...The DAX given in the description works great!

@justlearnitquick 2 жыл бұрын

You are a Goddess.

@shivangdubey2494 2 жыл бұрын

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

@dharanidhar4875 2 жыл бұрын

Hi,Can we do something in a table visual?

@jennytumay 2 жыл бұрын

Yes this should work with the table visual as well! In my comment back to ayşe i pasted the code to add onto the measure to account for totals since the one in the video doesn’t include that

@TocaDefne4 2 жыл бұрын

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

@jennytumay 2 жыл бұрын

Hi :) ive been working a lot and went on vacation with my friends!! Don’t worry i’m still here 😁 more videos to come

@TocaDefne4 2 жыл бұрын

@@jennytumay How about a video about dynamic column and dynamic row with two different slicers.is it possible?🤔So user can create their own report.And make it show which measure should appear or not.

@jennytumay 2 жыл бұрын

@@TocaDefne4 that sounds like it could be possible :) i have some other videos planned for the next coming months but this is a great idea thank you