Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bsheffer
Continued Contributor
Continued Contributor

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
bsheffer
Continued Contributor
Continued Contributor

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
bsheffer
Continued Contributor
Continued Contributor

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!

amitchandak
Super User
Super User

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

 

bsheffer
Continued Contributor
Continued Contributor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.