Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a matrix with one row (Hierarchy2019_tier2). tier2 is a page filter as well as tier1. Columns are year using a date table on the donation table.
I have a measure that returns a rankx on total sales volume.
sales volume rank cy =
if(ISINSCOPE('Dim View_MID_Properties'[Hierarchy2019_TIER2]),
RANKX(
ALL('Dim View_MID_Properties'[Hierarchy2019_TIER2], 'Dim View_MID_Properties'[Hierarchy2019_TIER1]),
calculate(
'Fact Cubes'[_Total_Sales_Volume]
),
,,Dense
),
""
)
I want to return the same rankx number regardless of any choice in the page filters Tier1 and Tier2. Choosing results with the Tier one page filter works great. However if I choose just one Tier2 value the rankx value is slightly higher than when no filter or only tier1 filtering is applied. If I choose more than one Tier2 page filter values it returns the same number as the unfiltered value.
Any idea why this is happening? Can someone suggest a fix?
Solved! Go to Solution.
add a filter in the date table removed the range change
calculate(
'Fact Cubes'[_Total_Sales_Volume],
filter('Dim Calendar ActivityMonth'
,'Dim Calendar ActivityMonth'[_ACTUALS RELATIVE YEAR FLAG] to 0
)
),
I'm not sure why that made a difference when it leaked at level 2, but it worked so win!
add a filter in the date table removed the range change
calculate(
'Fact Cubes'[_Total_Sales_Volume],
filter('Dim Calendar ActivityMonth'
,'Dim Calendar ActivityMonth'[_ACTUALS RELATIVE YEAR FLAG] to 0
)
),
I'm not sure why that made a difference when it leaked at level 2, but it worked so win!
@bsheffer , Measure Rank depends on what you choose in visualization and it is context-specific. Ideally, the lower granularity will govern in this case. If that because of the combined value. The value may change with context.
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
isn't the point of ALL('Dim View_MID_Properties'[Hierarchy2019_TIER2], 'Dim View_MID_Properties'[Hierarchy2019_TIER1]) to eliminate that context?
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |