Forum Discussion
Issue with RANKX function with "Don't Summarize" option
I am facing the issue while ranking the data based on a "Date" column to get the top 2 categories for each category using the RANKX function. Below is the sample data:
I am getting the below output using this DAX measure:
The above measure didn't work as soon as I opted "don't summarize" option for any of the columns starting from AAA3-AAA11.
Can anybody let me know why this is happening?
Any help will be highly appreiciable
Hi, Anonymous
Create a measure 'Rank' as below, then sort by column rank:
Rank = RANKX ( GROUPBY ( ALLSELECTED ( 'TestData' ), TestData[Category], TestData[Date] ), RANKX ( ALLSELECTED ( TestData ), CALCULATE ( MAX ( TestData[Category] ) ), , ASC, DENSE ) + RANKX ( GROUPBY ( ALLSELECTED ( 'TestData' ), TestData[Category], TestData[Date] ), CALCULATE ( MAX ( 'TestData'[Date] ) ), , ASC, DENSE ) / CALCULATE ( COUNTROWS ( 'TestData' ), ALLSELECTED () ), , ASC, DENSE )Please check my sample file for more details. If it doesn't work, share a pbix file on cloud storage for further research.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
- amitchandak
Super User
Anonymous , top two categories per date based on?
Do need measure rank or column rank can do?
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-columnsamitchandak - Amit I need the top two categories based on date only.
For that, I need a RANK measure.- amitchandak
Anonymous , Create a measure like this and use that in the visual level filter to check <=2
countx(filter(allselected(Table), Table[Category] = max(Table[Category]) && Table[Date] <= max(Table[Date])),Table[Date])
amitchandak - Thank you so much. It's working now for me. Can you please explain this to me to understand?
amitchandak - Amit Thanks for your help. As of now, it's working for me. Along with this, I want to sort one of the columns based on another column before giving the rank. Can you help me in that?
- amitchandak
Anonymous , You can sort column on the column, but not a column on measure or measure on measure
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
amitchandak - I have gone through this article as well before but in my case, I need to sort a column within a category as I was trying to get the rank within categories.
amitchandak - Amit can you help me to sort the data within the group as well?
- v-easonf-msft
Community Support
Hi, Anonymous
You need to sort column 'Category' first, then hold down 'Shift' and click another column (column'Date'/column'Rank') for secondary sorting.
Best Regards,
Community Support Team _ Eason
