Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

DAX: TOPN Function Error

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: Image and video hosting by TinyPicI don't know why it produces a warning. Is it because of a wrong logic or execution? Any suggestions would be great, thank you!
8 REPLIES 8
ryan_mayu
Super User
Super User

@Anonymous

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Cherry!


I still haven't figured it out. Can you pls help me? Here is the code that I made.

Image and video hosting by TinyPic

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:

Image and video hosting by TinyPic

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:

Image and video hosting by TinyPicImage and video hosting by TinyPic

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

Hi @Anonymous,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi cherry!



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

Image and video hosting by TinyPic

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

Hi @Anonymous

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  

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.