cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Rankx returns a different number depending on # of page filter selections

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Helper III
Helper III

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!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

isn't the point of ALL('Dim View_MID_Properties'[Hierarchy2019_TIER2], 'Dim View_MID_Properties'[Hierarchy2019_TIER1]) to eliminate that context?

Helper III
Helper III

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!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors