DAX: TOPN Function Error

[jtstacruz]

Hi all! A beginner here and I would appreciate the help 🙂 I need help in creating a measure in DAX and I don't know if my execution is correct. The thing that I would like to happen is to produce a graph of the Top 5 Assignee's (from the highest to lowest) who have the most number of "In-Progress" state. I started with countrows then the filter function because I needed to filter "In-Progress" in the status column. Then, I included the topn measure for the top 5 assignees. Here is my measure: I don't know why it produces a warning. Is it because of a wrong logic or execution? Any suggestions would be great, thank you!

[ryan_mayu]

@jtstacruz

If you don't want to do the settings in the visualization. I think you need to create a new table.

Table= TOPN( 5, FILTER(Final, Final[Status]="In-Progress"), Final[Assignee],ASC)

I am not sure if this coding can work. You can have a try.

Thanks

[jtstacruz]

Hi Ryan!

The code still doesn't work. It has an error that says, "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[v-piga-msft]

Hi @jtstacruz,

Have you solved your problem with the suggestion of AlB?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

[jtstacruz]

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!

[yelsherif]

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

[v-piga-msft]

Hi @jtstacruz,

Thanks for your desired output.

Please share your data sample which could reproduce your scenario so that I can copy and test on it.

Best Regards,

Cherry

[jtstacruz]

Hi cherry!

Thank you so much for your reply. Here is my data sample:



Let me know if you need additional details. I super appreciate the help 🙂

[AlB]

Hi @jtstacruz

I'm not sure I understand completely what you need but I think a good option would be to create an additional calculated column in which you rank the assignees by the number of "In-Progress". Probably with RANKX