cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtstacruz Frequent Visitor
Frequent Visitor

DAX: TOPN Function Error

Hi all! A beginner here and I would appreciate the help Smiley Happy 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
Super User
Super User

Re: DAX: TOPN Function Error

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  

Community Support Team
Community Support Team

Re: DAX: TOPN Function Error

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

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.
Highlighted
jtstacruz Frequent Visitor
Frequent Visitor

Re: 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.

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 Smiley Happy Thank you!
Community Support Team
Community Support Team

Re: DAX: TOPN Function Error

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

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.
jtstacruz Frequent Visitor
Frequent Visitor

Re: DAX: TOPN Function Error

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 Smiley Happy
ryan_mayu Established Member
Established Member

Re: DAX: TOPN Function Error

@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 Frequent Visitor
Frequent Visitor

Re: DAX: TOPN Function Error

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."
yelsherif Member
Member

Re: DAX: TOPN Function Error

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 309 members 3,032 guests
Please welcome our newest community members: