ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- Check out this awesome Power BI tutorial on how to add a filter by a measure! This isn't possible by default, so I will show you how to work around it and slice by a measure!
To enroll in my introductory or advanced Power BI courses:
training.bieli...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite
First, you came to the point and solved the problem of getting measure in a slicer. Then you anticipated all other aftermath problems I might face and solved that. GENIUS!!
This is brilliant! I've been stumped for weeks trying to get around the measure as slicer limitation. Amazing.
Great video. I am coming into this quite late so you may have update this already, but just thought i would add the following: Once that generated value table is created you can easily apply simply calc columns to give you a text based slice option. This works really well for me in a similar example where the sales value is then grouped into "0-100k", "101k-499k", "500k-1mil" etc etc and one can then slice using this column and it works exactly the same was as using the value column in the final slicer :)
Hi, can u explain in detail how u can achieve a text based slicer? Sry, I m a newbie at power bi
Good stuff parker. Having a "values" between filter is a great integration for many reports
Thanks Reid! I'm going to see where I can add this to existing reports.
This DAX tricks are something that really add up to your channel, please keep it up!
Thanks Ary!
Great video! How to set slicer to show only two options: measure vaules less then 0 and values greater than 0?
I need to know this too
Excellent video. I've implemented this for my report and works perfectly. What would useful now is a count of the entries returned by applying the slicer...this is proving more difficult than I expected. So taking your table at (1:47 into video), the return value would be 11. Any ideas on how to get this in a card visual?
Awesomeness. thanks for the video. I am getting hung up on the last part where the slicer range gets autoupdated. When I convert slicer to table, I can see filtered range correctly but when switching back to slicer it shows entire range for slicer. Any thoughts?
I've Got same Issue, slicer won't update min or max based on other filters even though showing correctly on Number filter table.. Any Ideas.
I always thought that there was a way to it, I could do everything but could make those slicers interactive.
This is great. You have an incredible brain
Hi this is very useful. Though it does not seem to work on Matrix tables. Would there be a work around for this?
This is super helpful, thanks!! I applied this to a date range, to filter by IDs who are associated with a Max Date (measure) that falls within a slicer period.
Thank you so much! I've been facing the problem of making the slider ranges dynamic and I just couldn't wrap my head around this problem; I've been fighting with this for days! Thaaaanks!!!
Great Trick to filter the slicer itself - that is definitely one to remember!
Thanks for watching Heather!
The internet remains undefeated. My case is I have a scatter plot chart and the end user asked if the zoom slicers could interact with another visual (table), allowing them to view a filtered list. This solution allows me to add that functionality.
Thanks for this video. Tried the same calculation for the range and got this error "Column 'p.Discount Value' in table 'p.Discount' cannot be found or may not be used in this expression". The p.Discount is my parameter.
Great work for a table, anyone have an idea why it doesn't work within a Matrix visual?
phew. I totally forgot about that last step on how to make slicers affect other slicers. Good thing you included it.
I wasn't able to fully mirror my table, because it pulled from multiple datsources. But I think it's working as it should........
I've been searching for something like this for away to create a dynamic sample data table. Thanks, very easy to follow
Really glad to hear, Raymond!
What if the table I want to filter is a matrix. With categories on the left, and then dates on the top? I would want to keep a row and all it's data if a specific measure was a specific value.
Excellent! 3 bullseyes!!! Brief, concise and precise presentation. Exactly the "how to" I am looking for. Thank you so much for sharing. More power to you.
Selectedvalue only returns a valid value if one value has been selected. In this case for a range in a slider will return blank because there are multiple values. So it won't work when trying to put the min and max range values dynamicly. So as soon as you put the filter to 1 in the slider i get an error.
My measures are complex iterative measures and it seems to completely mess up my table visual when I add the measure filter. I think that it's iterating every row again and thus I go from 179 rows to 179 x 179 rows. Is there something I can to stop that behaviour?
I tried to implement the solution in the video and the second part of it does not seem to work. If I apply the filter on the slicer based on the second measure created (called Slicer Filter), the slicer will always be filtered for the numbers that are selected in the slicer. I guess it is because the Slicer Filter measure value is blank in the cases that are out of the selected range. Can it be that the behaviour of Power BI has changed since the publication of this video and so filtering the slicer to the minimum and maximum values does not work anymore? Or am I missing something?
Is there a way to set a bookmark that would reset to the default values?
I have a chart with data. There is a slicer for the chart that filters data by date. Can I create a single-selection filter that will automatically be set to the most recent date?
Thanks! what if i want to count these occurrences in a card visual , means in your example count the months rows in your table visual? or actually its the sum of this MEASURE FILTER..
Incredible!! Cheers from Brazil!
Thanks for the video! I was able to use this to design a slicer that filters by the active logged-in user! Awesome!
Sounds awesome Dan!
Cool trick ... but I struggling to get my head around the usefulness of this technique. Could you provide a couple of exmaples when/why you would use this in a report? Thanks.
Always a good question. I have the immediate requirement to filter a work report by employees who have worked 30-40 hours, for example. Imagine that they have multiple 8 hour time entries that sum for the week. I need to filter to only show workers in the desired range or maybe even low-performers.
A very neat trick, I was searching for it so so long
Apologies, but isn't there an issue at minute 10, when we're filtering and our slicer's minimum value becomes 2000, yet we can see values lower on the left in the table ? 10:01
thank you very much. this is the practice I am looking for. but i could not make the slicer limit correct 😭. I have tested the MaxValue of slicer filter. the number is correct. but when I put it into filters and let it to be 1. the maximum amount is incorrectly bigger....i hope if anybody here could help to provide some more clue to check what's wrong.
Do you know why this isn't working for a percentage range slicer? It just greys out the min/max values in the slicer and they can't be edited...
As always, crisp and clear explanation. Thanks mate for amazing video
Thanks for that, Mohit :)
Good idea. But for some reasons, it only calculates for the first few rows and marking as 1 using the DAX and leaves the rest as 0.
Thanks this worked great for me. Im using this solution in a matrix and finding that its including the row total as part of the filter. Is it possible to only filter based on values excluding measure/row total
Thank you sir, i have utilization column from 1-130, but in the series i am getting till 74. what could be the reason?
Thank you so much for clarifying this concept!
No problem Pallavi!
Great trick!
but what if i want to create a slicer for several measures? is there an efficient way of doing this?
Hi Parker--This is great! I have implemented this in my model and it works perfectly. The one thing I can't figure out is how to count the items in the resulting table in a card. Can you please assist with this? Thank you!
thats my issue as well. did you manage to solve it?
hey i want to ask, how to do filter by measure in a slicer, but the measure value is on string format?
Great example! Looking forward to using this.
Hope it serves you well :)
what is DAX function that creates Total Sales???
This filter is not working when I create calculation group 😥
Thanks for great video. But there is an issue with this work around. Say you have multiple 'native' slicers where you filter down your table and calculate average. When you use this slicer to filter although the table changes the average wont.
Great video to show the use of a measure in slicer?I have a similar requirement where i need to calculate the total count of distinct rows , in your above example cityname or category name.I tried but not able to get the distinct count.Can you suggest ?
I would love for you to post the demo files to download so we can follow along! But thanks for the video anyway
this is awesome...I've been meaning to do something like this for awhile. Great work as usual! I wish PBI just did this as a default....maybe they will at some point. :)
Thanks Nate! Hoping for that day ;)
@@BIEliteunfortunately still not the case, Microsoft try to do complicated things (AI,..) but not focuse on simple and usefull features like this one or for example having MAX date/period selected by default in a slicer...etc
So we need to do complicated and NONE PERFORMED workaround to do this SIMPLE functionnalities....
Just asking - why do you not make a connection betweeen sales and new table?
one to many and no need filters :)
Wow! This is awesome. Fantastic. Mega thanks for that. Yeah, I'm excited!
Really glad to hear, Erica :)
How do you create the sales volume and Quantity filter?
Thanks! I had to do something similar but on a True/False measure. So I created the table:
ActivityTable = DATATABLE ( "Activity", STRING, { { "True" }, { "False" } })
and the filter:
Measure Filter =
VAR minValue = FIRSTNONBLANK(ActivityTable[Activity],1)
VAR maxValue = LASTNONBLANK(ActivityTable[Activity],1)
VAR TotalDrops = if (
(ISBLANK(SUM(Fire[numberOfDrops])) || SUM(Fire[numberOfDrops]) = 0), "False","True")
return
if (TotalDrops = minValue, 1, 0)
and hardcoded the measure filter = 1 on the visual, and BAM, I was able to toggle between seeing entries with no Drops versus entries with drops. Thanks!
amazing stuff - hope i can use this technique to fetch min date from a slicer and take average value of that min date as reference to arrive at % change from that reference date
Hi,
I am having following is.
I have quarter slider a page. And when I drill down to other page I want to see data for last 3 quarters for the selected quarter year in first page. How to achieve this. Can you please help
Again, very clever. There is a similar pattern when you have categorical data ie growing/declining or above/below average rather than a series using containsrows().
I would love to hear specifics about this pattern. Sounds very interesting!
@@BIElite Can I send you a link to a model?
Hi, Thanks for the video, quite informative. However, when I am trying to do the similar measure as shown for a date slicer - to change the date range dynamically based on another filter it doesn't change the date slicer. If I apply the same measure on a table with the dates it does change it but doesn't do the same change on the date slicer. Will be greatful, if someone can assist.
Thanks in advance.
Can you please help clarify how the SELECTEDVALUE function works with that range of values? I'm having trouble implementing this because the Value column in the Number Filter table doesn't have one distinct value, so it returns the alternate result.
Excellent advise. Many thanks.
Great video, mate. In our situation we're working with a live connection to a dataset, so we do not have the capability to add columns or view the Data view. We can only create measures, which means we cannot add a slicer to provide the sum sales range as you have shown here. Thoughts?
Great Idea of Filters and Use of Measure SLICER!
Thanks Parker!
gab
Thank you Gabriel! I was ecstatic when this worked out.
@@BIElite Ohh Yeah is a very powerful non-documented feature, don't you?
Brilliant - just what I needed. I actually want users to select items but I want to give them 3 alternative parameters to define. With 0 / 1 measures I can even let them subselect from items defined by any one parameter out of 3, or by 2 out of 3 or only all 3 by adding the measures. V cool
Thank u,Quick question that Total sales is different for each rows right? So how did u calculated it? Actually I need to do this filter based on a column value how can I implement it?
Hi,
Great Thanks for sharing this trick, it is really useful, but could you please suggest a way out when we apply any filter as suggested in the trick can we count rows in the table visual dynamically as well.
Thank you !!
this works well with the generate series table. thx. But it doesn't seem to work with a parameter table where i filter from a number starting at 0 (not a range). When I call the value column from the parameter table, the measure gives me a (value column) error. Is it possible to do this from a parameter table/value column?
correction: i got my issue to work with a parameter table (for distance KM and points on a map) using maxx. I'm not 'in between numbers'. Rather, I'm just sliding by increments of 50km from 0 as the permanent starting point. works! Thanks!:
Table Filter =
var MaxValue = MAXX('Maximum Distance (km)','Maximum Distance (km)'[Distance Value])
var currentMeasureValue = [Distance (km)]
return
IF(currentMeasureValue
How about a slicer based on growth? Is it possible?
Easy, fast, and very usefull! thanks so much!
Is there really not simple "connect slicer to graph" kind of solution?
HI, just one question, Is measure filter is applicable on card visual? I'm excited to learn more on this
Is there a work around for passing calculated measure like avgsales = AVERAGE(SALES) into generateseries? genSer = GENERATESERIES(0,avgSales,1).. I tried this but the avgSales somehow stops working properly in the generateseries function. Is there a solution to this?
Amazing video, super clear and short. Thanks a lot!!!
WOW, this is an amazing insight. It does requiere A little of work around, but totally worth it. Thanks a lot!
This was very helpful! Thank you so much for sharing your insights.
No problem!
How to do something like this but date?
Christmas Came Early !!! thanks Parker!
Haha that's how I feel too!
Hi! This video is awesome and it helped me solve an issue where I wanted to filter airplanes by the number of hours they fly. Now I would also like to use a slicer to filter these airplanes by their current status (for sale, for lease, pending, etc.), and I use a measure to output the status because it depends on several parameters. So I am wondering how I could do the same thing, but with a measure that is not numerical like in your example. I can't create a series for my measure's results because they are qualitative and not quantitative values. Would you be able to help me on that?
Excellent technique. Thanks for sharing!!
Of course, Daniel! Thanks for the watch.
This is amazing! I have been looking for this for forever!
No problem Zachary!
Thank you so much, I was really struggling with this one 💯
No problem!
Thank you so much. Hello from Brazil.
This is fantastic! Great video. Well explained and articulated! Thank you so much.
How I can get the pbix file?
Amazing! Can't believe power bi still doesn't allow measures as slicers
Why not just use the filters pane for this ? (only include desired columns in filter pane to keep it clean and simple)
That doesn’t offer the same functionality, unfortunately.
Thank you so much for this insightful video.
Thank you, this helped me a lot. Appreciated!
This is a really great solution for a common problem, Could you pls share the Solution file
It really worked for my use case. Thank you so much!
I just watched this, and it is awesome. I have a problem with the slicer filter not displaying the max values. It stops at around 200 instead of going to 9999 which is a default I have when my measure formula wants to return infinity. I have tried retyping the generate series code. Any ideas?
I should say that the max value of the measure is about 119K. But it only gives me values to 200. Thoughts?
This is just awesome Patrick!
It's Parker, but I get Patrick like half the time 🤣
Absolutely Brilliant!!! Thanks a ton
No problem!!
thanks ! It was exacly what I was looking for
Thank you!! This was so helpful.
For some reason, Power BI does not support a GENERATESERIES() function including a measure, I had to do it with static numbers (1,100,1)
beyond helpful thank you so much
No problem, Janet!
Excellent video! Thank you!
This solution worked for me in my matrix, but did not work on other visuals where the same row category was not specified. I found another video that recommended creating groups for my category and that worked! So I used your first measure and evaluated it against a custom table to then filter my graph and cards as well. The trick was creating a dynamic group from the visual in your video. Here is a video on how to create dynamic groups th-cam.com/video/jLloQ_1KiGI/w-d-xo.html
Excellent trick!
Thanks!
This is exactly what I was looking for!!
THANK YOU SO MUCH!!
Thanks
Hey thanks!
@@BIElite Legendary DAX & video. Well blooming done!!!