Forum Discussion

jjones90274's avatar
jjones90274
Frequent Visitor
3 years ago

DAX RANKX by Quarter

I am having issues creating a RANKX function that ranks values that are in the same period. My data contains multiple cities with multiple years and quarters for each city (Ex: Dallas for 1Q 2021, 2Q...
DAX
Need Help
RANKX
RANKX by quarter
amitchandak's avatar
amitchandak
Super User
3 years ago

jjones90274 , Try measure like

 

Rankx(allselected(Table[City]) , CALCULATE(SUM('Costar Data'[Vacancy Rate])), , desc,dense)

 

Rankx(filter(allselected(Table[City], Table[Qtr Year]), [Qtr Year] =max(Table[Qtr Year])  , CALCULATE(SUM('Costar Data'[Vacancy Rate])), , desc,dense)

 

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

jjones90274's avatar
jjones90274
Frequent Visitor
3 years ago

Thanks for the reply @amitchandak! I gave the first measure a try and I am still getting duplicate rankings (see below)

 

 

 For reference I have included a snapshot of the relationships I have in the model.

 

The data I am using has both past dates with historical data and future dates with forcasted data. I tie the two data sources together through the calendar and market key tables. In my report I have slicers for Date (Year and Qtr) and Market.