cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Helper III
Helper III

Re: Rankx returns a different number depending on the function of the page filter selections

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
Highlighted
Super User IX
Super User IX

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

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

Highlighted
Helper III
Helper III

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

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

Highlighted
Helper III
Helper III

Re: Rankx returns a different number depending on the function of the page filter selections

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

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors