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.
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)
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! 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!
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 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
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?
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 :)
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.
Is there an option where you build slicer by a measure WITHOUT adding new table (I have this option disabled - it is not mine dataset) . I can only create measures.
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 .
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.
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 ?
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?
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!!
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!
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).
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?
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!
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)
@@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.
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.
![เปิด อาณาจักร หมื่นล้าน "สาระตั้ม" l [Nickynachat]](http://i.ytimg.com/vi/3XE0nPF4YkQ/mqdefault.jpg)
![F.HERO Ft. JSPKK x ลำไย ไหทองคำ x M-PEE - ไม่สนิทบิดหมด (Thai Riders Anthem) [Official MV]](http://i.ytimg.com/vi/kPJZ0UihBfk/mqdefault.jpg)
Cool trick. Is it possible to get a copy of the .pbix file? Thanks.
Didn't I share it? Sorry, on my way!
@@CurbalEN Cool! I also looked for this pbix file. Can you share it with us? :)
It is in the download center at curbal.com (look in the DAX folder)
@@CurbalEN hi Ruth could you send the link to this PBI? Can't find it somehow, thanks!
Found it. Thanks!
Some weeks ago I pitched the very same question at guyinacube. Answer: Not possible. Now Ruth shows us. Amazing!! LOVE IT!
The power of the community 😊
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.
How clever is that?? I need to test and do a video:)
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)
Thanks for putting up with me for so long!! 😊
@@CurbalEN lol...it's really the pleasure of us all
Incredible - just what I was looking for. Many thanks for this!
I love your accent I just watch your videos to hear you talk, so entertaining.
😂😂 why not? Check my early videos when I was filming at 11 pm. People always commented how they enjoyed my voice !
@@CurbalEN ❤
Very cool! Knowing DAX is so critical to Power BI's flexibility!
Agree!!
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! 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!
Thanks, I have learned today how to add filters on Measures.
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 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
Thanks for sharing;)
This was just what I needed, Ruth! Thanks so much!
Excellent!!
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?
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 :)
😊
Hello! I need your help( . Is it possible to do dynamic abc, xyz analysis in power bi, but I should use slicer for categories(a,b,c). Please help
Great video Ruth! Is it absolutely necessary to create the variable in this approach? Could you have just used the if/switch statement?
Thank you! This was very useful and thank you for the pbix files. I will definitely be subscribing to your site
Welcome!
Great solution and works well with so many scenarios! Thank you for sharing. Truly inspiring!
😊😊
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.
Is there an option where you build slicer by a measure WITHOUT adding new table (I have this option disabled - it is not mine dataset) . I can only create measures.
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 .
Happy Friday Ruth, Bravo! This is really amazing solution. Mark! Thanks for your sharing. 🎉🎊
Yey!!! Happy to be able to still surprise you :)
Just simply beautiful, thank you so much!
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.
Yes, the thing is, I want to bucket to be calculated dynamically, but I didn't get that far due to lavknof time ;)
Where did you get that super cute denim ruffled top?? And thanks for the cool trick.
I got it from my syster, so I don’t know :(
Got it a long time ago also, not sure you can buy it now.
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 ?
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?
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!!
Great question! You can't compare text with numbers, try it, you will see!
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!
Try with ISFILTERED or IN , that might do it.
@@CurbalEN Great video but can't seem to get it working with multi-select, are you able to kindly share the DAX code for this please?
Great trip trick, Ruth 👏🏻
Yey!
Could you make new table for summary total for each color? is it possible? please answer me 🙏
Great video Pbix file is it available to look at it?
Yes
Thanks, this is very good real life example where Powerbi can add value with very little effort, if you understand DAX 😂
Yes, you need to know some DAX ;)
Super, thank you , &&
how to make a dynamic ranks in a slicer¿
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).
Yeah, I thought that too, but when I tested it it didn't quite worked as expected. Try it and you will see.
Great video!!
Thanks!
If we need two selections in slicer(for e.g red and orange) then how my dax will look?
Try isfiltered or IN
Very nice video, great work!👍
Great trick. But what if user select 2 or may be 3 values in slicer? It will not slice it I think?
Disconnected slicers are there for some time, but yep, that's a good one. ;)
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?
Post sample data in the power bi community. Details are important here.
@@CurbalEN Cheers. Was going to use the Forum next step.
It is the right way to go, details are needed to answer your question. Enjoy your evening!
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!
The dataset includes the number of bikes per station, so that should help, check it out!
Thank you for sharing . Great video.
🥳🥳
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)
Could you sometimes make a video about why we need to create a separate table, I am not very clear on that.🙁
In this case, it is easy: you can not use a measure for relationships.
Top !
Excellent video! you have a small bug: You didn't cover the cases for =1000, =5000.. it should be
The 1000 is included in the orange button ;)
@@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.
Bravo!
🥳🥳🥳
Great looks and t(r)ips! ✨🥂
Awesome 🙂🙂🙂
Loved it too :)
You are so grrrrrrreat
Thanks!
you should use greater or equal than. if the value is 1000 you will lose it.
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
I completely agree, but if you need it, this is how to do it, that is all :)
@@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.
@@garyjames2931 do you have a better solution to filter out measures?