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)
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.
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 :)
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?
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!
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.
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!
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?
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...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
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 .
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!
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, 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!!
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)
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!
@@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.
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?
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.
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 😊
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
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:)
Very cool! Knowing DAX is so critical to Power BI's flexibility!
Agree!!
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 ❤
This was just what I needed, Ruth! Thanks so much!
Excellent!!
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 :)
😊
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?
Great solution and works well with so many scenarios! Thank you for sharing. Truly inspiring!
😊😊
Happy Friday Ruth, Bravo! This is really amazing solution. Mark! Thanks for your sharing. 🎉🎊
Yey!!! Happy to be able to still surprise you :)
Thank you! This was very useful and thank you for the pbix files. I will definitely be subscribing to your site
Welcome!
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;)
Just simply beautiful, thank you so much!
Thanks, I have learned today how to add filters on Measures.
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!
Great trip trick, Ruth 👏🏻
Yey!
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.
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!
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?
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 ;)
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 ;)
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
Great video!!
Thanks!
Very nice video, great work!👍
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?
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.
Thank you for sharing . Great video.
🥳🥳
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 .
Could you make new table for summary total for each color? is it possible? please answer me 🙏
Disconnected slicers are there for some time, but yep, that's a good one. ;)
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!
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?
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.
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!
Great trick. But what if user select 2 or may be 3 values in slicer? It will not slice it I think?
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)
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!
Super, thank you , &&
how to make a dynamic ranks in a slicer¿
Top !
Great video Pbix file is it available to look at it?
Yes
Great looks and t(r)ips! ✨🥂
If we need two selections in slicer(for e.g red and orange) then how my dax will look?
Try isfiltered or IN
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.
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!
Bravo!
🥳🥳🥳
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.
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?