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