You can also use this technique to hide and show measures in something like a line chart. To do multi-select, in each measure you add a variable that checks if the status is filtered in, and return blank or 1 to the variable. You then multiply your calculation by the variable (or do an if statement), and it will return blank or a value. I used this when I had to many measures on a line chart.
Hi Ruth, great, this is a part of my powerBI advanced level 3 session. In Dutch. Keuze = IF(ISBLANK(SELECTEDVALUE(Resultaattype[Type]));"maak keuze";SELECTEDVALUE(Resultaattype[Type])) maak meting die resultaat laat zien: Resultaat = SWITCH(SUM(Resultaattype[Code]);1;[Kosten];2;[Omzet];4;[winst];[omzet]) Or GekozenValuta = IF(HASONEVALUE(tblValuta[Valuta]);VALUES(tblValuta[Valuta]);"EURO") TotalSales Valuta = IF(HASONEVALUE(tblValuta[Valuta]);SWITCH(TRUE(); [GekozenValuta] ="EURO";FORMAT([TotalSales]*VALUES(tblValuta[Koers]);"€ 0.00"); [GekozenValuta] ="BPND";FORMAT([TotalSales]*VALUES(tblValuta[Koers]);"£ 0.00"); [GekozenValuta] ="ZWFR";FORMAT( [TotalSales]*VALUES(tblValuta[Koers]);"F 0.00"); [GekozenValuta] ="DOLLAR";FORMAT([TotalSales]*VALUES(tblValuta[Koers]);"$ 0.00") );BLANK()) And then create variabels from this
Hi! Do you have a video with a similar example but for multiple slicers? Here you have one slicer but in my use case I have 3, multi-select but if I can get it to work for single select that will be fine also. Tia!
Very cool trick, found it useful. But a limitation I experienced with it is that total records in the table remained same, they were not filtered out. Is it possible that table will show rows only for Green when Green is selected on slicer?
I love this technique. However, I am running into a bit of trouble similar to what I see @10:13 where you have green selected but no total is bottom of the table. For me, I have "Top20" and "Others" in filter. And, when I have nothing or "Top20" selected, I get a total at the bottom. But, when I have "Others" selected, the total is like in your table. Is there a way to fix this? BTW, I was able to get the total to show for "Top20" only after I added the measure to the visual filter and selected "is not blank". Edit, ah I figured it out. Just use the regular measure in the table while having the "Measure by Slicer" in the table filter pane works.
Thanks, Ruth. Very useful. Just a thought - could you have left out the 'greater than' conditions in the SWITCH() steps 2 and 3? I thought it evalauated the conditions in order so that the one being < 1,000 would already have been counted before looking at those < 10,000 (i.e. so condition 2 would be for those > 1,000 and < 10,000).
@@CurbalEN In the #trips by slicer formula, 1 is less than 1000, 2 is more than 1000 && less than 10,000 (same for 3 ). You do not cover the cases where the values are equal to 1000,10,000 . For 50,000 its correct. Look at the video at 8:13.
Hello, is it possible for this slicer to filter table? if you choose green then table will show records with green only? And is there a way to choose all slicer fields and show them all in table? Thanks!
hI Curbal...Your Dax formula has help me alot...can u pls explain in your table view ...how to filter only the slicer selection keeping both feilds in your table ie # trips and also # trips by slicers but showing in the table only the filtered # trip by slicer...thanks
Hi @curbal, Nice video, really helpfull. I only have one concern, is it possible to show all data if the filter is not selected. If possible would you like to writing the dax on comment ?
Really nice! A question comes to mind: Is there a way to show more than two results (colors) on the map when selecting 2 o more values on the isolated slicer? Thanks in advance!
Dear Ruth, after following you for more than 2 years, i find your enthusiasm in every video fantastic. Please don't change!! 😁 (another great video by the way)
Great video Ruth, I have been following you since ages. I had spent my all day applying your trick . It is working fine for matrix where we can pass the filter condition but cards visual don't get filtered accordingly. Please let me know if you have a solution for that as well .
Hey Ruth! I have made a transition successfully from a software engineer to a Power BI developer. All the credit goes to you as I started learning Power BI, from the first video of yours till now ❤️ Keep going! I have a requirement of taking the measure values which are decimals, and show it as a range slicer. When I select a range in the slicer, the visual gets filtered according to the selected range of values. Is that possible?
Hello, Thank you for good tips, I tried to bring current month and current week in one slicer by using this tech, but it doesn't work, could you please let me know how I can do this? "I made two columns in my calendar("current week and current month", and put each one in one slicer, and it works when I press "current month" it shows exactly what I want. and when I press "current week" it shows good. but they are in different slicer. I tried to bring both in the same slicer by using your technique, but it is not working. " order slicer = Var timetotal =[Total Time] Return If( HASONEVALUE('Table'[name]) ,SWITCH(True(), Values('Table'[number])=1 && VALUES('Calendar'[Current Week])="Current Week", timetotal, Values('Table'[number])=2 && VALUES('Calendar'[Current Month])="Current Month",timetotal),timetotal)
Thanks Ruth for sharing this tip. It is just cool to pick an Austin dataset for presenting in Austin User Group! I have always found it useful to make it as relevant as possible to the audience when it comes to Power BI! You took that to the next level :)
Didnt work for me. Way way to complicated for a simple filter. Better to prepare al this before even loading into power and dont touch dax measures merged or any of this horrendous witchcraft
@@CurbalEN Yes agree - wel done for finding a solution but it should be so much easier. The idea of PBI is that a user can do these things and not a clever coder like you.
Hi Ruth, I have a question. Why are you using the limit field on the switch formula in stead of the status name (ie: orange,red,green...etc) Is it related with performance? Thanks for the video I am sure I will be able to apply in some of my current project!!
Nice application 👍 I'd suggest setting up the disconnect table to include min & max limits instead of (1,2,3...) and use these limits with SELECTEDVALUE in the measure.
Pretty cool! I know we are using trips as a rough estimate to determine best time and place to catch a bike. Makes me wonder what the ideal metric would be. Probably available bikes? From the video I don't see that metric but I wonder if we could back into it or at least a relative comparison among stations since we have the start station and end station. Each station could get a -1 for a trip starting there and +1 for a trip ending there. Higher the score the better the chances. Fun to think about! Thanks!
Hi Ruth. Enjoy your channel. I have tried to apply this method. it works for the main table on my table. but I am trying to then count and average the results that it returns. This does not seem to work. I am using the method to group floor areas in to "Small, Medium and Large". I then have a calculated column to work out a price per area and it this column I want to average. The measure I sue for this is a divide of the sum by the count, but it ignores the key slicer selection. suggested learning resources?
