Forum Discussion
DAX: TOPN Function Error
Hi Cherry!
I still haven't figured it out. Can you pls help me? Here is the code that I made.
I know that he suggested RANKX as a new calculated column, however I still don't get the execution I should do. This is my desired output:
With the measure I created which is seen in the first photo, I was able to filter the Assignee's and the status = "In-Progress". However, I would want to include a TOPN dax measure to get the top 5 assignees with the most number of in-progress status and display my desired output. I know that there's an option wherein you can do it using Visual Level filters:
But I would really love for everything to be created as a measure. And not use the visual level filters anymore. Please let me know if you could suggest a measure that I could use for this :) Thank you!
If you want to show exactly 5 results, you will need to create a special ranking calculation. The ranking function in Power BI treats similar values as one. in your example, Benjamin and Concepcion have the same value and will be both considered as the 2nd rank, thus the chart will show 6 items instead of 5.
To overcome this you will need to have a 2 level ranking which relates to value and also alphabetical order so that Benjamin will be ranked 2nd and Concepcion is ranked 3rd.
Assuming your table is tableName, values are theValue and name is theName
1. create a new measure like: ranking = format(tableName[theValue], "000000") & tableName[theName]
2. create the bar chart with theName in the axis and theValue in the values
3. Add theName to visual filter and select TopN
4. Select show Top 5
5. Drag the "ranking" measure in the "by value" area
Explanation: this method creates a numeric and alphabetical ranking by creating keys like 000020May, 000013Benjamin, 000013Concepcion, 000010Julius, 000004Angli. These keys can be sorted descendingly in alphabetical order without mixing similar values, ...13B... comes before ...13C...
I did this trick many times, hope it works for you