Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I've been stuck on this problem for a few weeks. You can find a sample of the data and the model here.
I have a Orders, Companies, and Dates table. The Companies table has a Parent/Child relationship that forms a ragged hierachy.
I have visualized it in a matrix to show YTD Revenue, PYT Revenue, YTD vs PYTD and YTD vs PYTD %.
I would like to be able to filter the visual for Parents YTD vs PYTD % that fall in a 10% range. For exmaple, I'd like a slicer that showed 100% to 90%, 89% to 70%...etc. and to be able to select one of these ranges to filter Parents whos YTD vs PYTD % fall in this range along with their childern, even if the childern have a different value.
I have tried using dynamic segemnations patterens, also tried createing a calculated table to compute the YTD vs PYTD % and use as a filter. I think my DAX skills aren't advanced enough, as I struggle conceputally how to tackle this problem. Any guidence is greatly apprciated.
Thanks!
Solved! Go to Solution.
Try defining a new parameter that has values from 0% - 100% and create a measure to filter based on those parameter values in a slicer.
I defined a new table named Range and defined this as a filter measure.
InSelectedRange =
VAR Level1Growth =
CALCULATE (
[YTD vs PYTD Revenue %],
ALLSELECTED (),
VALUES ( Companies[Level 1] )
)
VAR RangeBuckets =
FILTER (
Range,
Range[Range] <= Level1Growth
&& ( Range[Range] = 1
|| Range[Range] + 0.1 > Level1Growth )
)
RETURN
IF ( ISEMPTY ( RangeBuckets ), 0, 1 )
See attached file.
Try defining a new parameter that has values from 0% - 100% and create a measure to filter based on those parameter values in a slicer.
I defined a new table named Range and defined this as a filter measure.
InSelectedRange =
VAR Level1Growth =
CALCULATE (
[YTD vs PYTD Revenue %],
ALLSELECTED (),
VALUES ( Companies[Level 1] )
)
VAR RangeBuckets =
FILTER (
Range,
Range[Range] <= Level1Growth
&& ( Range[Range] = 1
|| Range[Range] + 0.1 > Level1Growth )
)
RETURN
IF ( ISEMPTY ( RangeBuckets ), 0, 1 )
See attached file.
Thank you @AlexisOlson, I am going to try and implement into my model. Question is there a way to filter a whole page for just the customers that fall in the ranage?
You could define a calculated column on the Companies table that buckets each [Level 1] into the corresponding range and then use that column for your slicer. This isn't dynamic though. Each [Level 1] item can only have a single value regardless of what report filters you have.
Ok I'll play with that and see if the end result gets me close.
I added your measure filter to my model and it works except if I have a year selected in my date slicer. Do I need to add some type of date filter in the Level1Growth variable?
InSelectedRange =
VAR Level1Growth = CALCULATE ( [YTD vs PYTD Revenue %], ALLSELECTED (), VALUES ( Companies[Level 1] ) )
VAR RangeBuckets = FILTER ( Range, Range[Range] <= Level1Growth && ( Range[Range] = 1 || Range[Range] + 0.1 > Level1Growth ) )
RETURN
IF ( ISEMPTY ( RangeBuckets ), 0, 1 )
I'm not sure what you're saying doesn't work.
This is an example of what I see with a year selected:
Is this not the expected output?
Strange, if you look at the pictures below both range filters are the same but populate the matrix differently. The only modification I made was to extended the range down to -1.
I also have one additional page level filter to filter out inactive customers but removing this didn't change anything.
Maybe because 2019 doesn't have a previous year?
Maybe you're right, I expanded my date table to include the full range of dates in the Orders table and I still have a blank in the slicer. It's a head scratcher....the only other thing that is different is I user Company Name instead Company Id for the levels of the hierarchy.
When I change the range slicer to less than zero the behavior is even stranger. The matrix shows positive percentages.
Hi @AlexisOlson, I've been trying to break you solution apart to see where it fails in my model. I think I found where the issue is coming from.
When I test the effect of selecting the year on the RangeBuckets varible I get the results below. When I run this test in your file, the RangeBuckets remain -100% no matter if a year is selected or not.
I tried using a REMOVEFILTER for the year but no luck. Any idea how to solve this?
I figured out the issue, it is two fold. First, I had a relationship in my model that created a circular dependancy. Second, the filter mease is calculating in 10% increments so it includes values greater than the max or min value in the slicer. I change the slicer to a drop down so the user can select a bucket of value in 10% increments which makes more sense from a usability perspective.
I can't access the file you linked to.
@AlexisOlson sorry, put the wrong type of permision in google drive. It works now, just checked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |