Forum Discussion
Need help with Topn Measure please
Hi, Using Table1 below, I'm looking to have a table visual in my report that looks like Table2, which shows the location with the highest number of issues related to it. Is there a way to do this using Top N? or another measure, please?
Table1
| Location | Issue | Issue_Number |
| Manchester | Aggression | Issue1 |
| Manchester | Aggression | Issue2 |
| Manchester | Aggression | Issue3 |
| Manchester | Aggression | Issue4 |
| Manchester | Neglect | Issue5 |
| Manchester | Neglect | Issue6 |
| Edinburgh | Neglect | Issue7 |
| Edinburgh | Violence | Issue8 |
| Edinburgh | Violence | Issue9 |
| Edinburgh | Violence | Issue10 |
| Edinburgh | Violence | Issue11 |
| Edinburgh | Neglect | Issue12 |
| Newcastle | Emotional | Issue13 |
| Newcastle | Violence | Issue14 |
| Newcastle | Aggression | Issue15 |
| Newcastle | Emotional | Issue16 |
| Newcastle | Emotional | Issue17 |
| Newcastle | Emotional | Issue18 |
Table2
| Edinburgh | Violence | 4 |
| Manchester | Aggression | 4 |
| Newcastle | Neglect | 4 |
Thanks
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a measure.
WINDOW function (DAX) - DAX | Microsoft Learn
Top 1 #count by location: = VAR _tononetable = WINDOW ( 1, ABS, 1, ABS, ALL ( Issue[Issue] ), ORDERBY ( CALCULATE ( COUNTROWS ( Data ) ), DESC ) ) RETURN IF ( HASONEVALUE ( Location[Location] ), CALCULATE ( COUNTROWS ( Data ), KEEPFILTERS ( _tononetable ) ) )Hi,Jihwan_Kim ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,RichOB .I am glad to help you.
Like this?In order to distinguish the data between Location groups, I added two extra lines of test data.
I created three measures and eventually created a calculated table to store the final summarized data
The measures I created:M_groupNum01 = VAR _lacation = MAX ( 'IssueTable'[Location] ) VAR _issue = MAX ( 'IssueTable'[Issue] ) VAR _issueNumber = MAX ( 'IssueTable'[Issue_Number] ) VAR _result = CALCULATE ( COUNT ( 'IssueTable'[Issue] ), FILTER ( ALL ( IssueTable ), 'IssueTable'[Location] = _lacation && 'IssueTable'[Issue] = _issue ) ) // or use ALLEXCEPT() function RETURN _resultM_maxNum02 = MAXX ( ALLEXCEPT ( IssueTable, IssueTable[Location] ), [M_groupNum01] )Top Issue = CALCULATE ( MAX ( 'IssueTable'[Issue] ), FILTER ( ALLEXCEPT ( 'IssueTable', IssueTable[Location] ), [M_groupNum01] = [M_maxNum02] ) )I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
These measures work
Predominant issue = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Issue] ), [Issue count] ),1)Measure = MAXX (VALUES(Data[Issue]), [Issue count] )Hope this helps.
6 Replies
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
It is for creating a measure.
WINDOW function (DAX) - DAX | Microsoft Learn
Top 1 #count by location: = VAR _tononetable = WINDOW ( 1, ABS, 1, ABS, ALL ( Issue[Issue] ), ORDERBY ( CALCULATE ( COUNTROWS ( Data ) ), DESC ) ) RETURN IF ( HASONEVALUE ( Location[Location] ), CALCULATE ( COUNTROWS ( Data ), KEEPFILTERS ( _tononetable ) ) )Hi,
These measures work
Predominant issue = FIRSTNONBLANK ( TOPN ( 1, VALUES ( Data[Issue] ), [Issue count] ),1)Measure = MAXX (VALUES(Data[Issue]), [Issue count] )Hope this helps.
Hi Ashish_Mathur thanks for getting back to me. Where exactly is [issue_count] coming from? Are you making a measure of the individual counts first? thanks
You are welcome. It is a measure
Issue count = counta(data[issue number])
Hi,Jihwan_Kim ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,RichOB .I am glad to help you.
Like this?In order to distinguish the data between Location groups, I added two extra lines of test data.
I created three measures and eventually created a calculated table to store the final summarized data
The measures I created:M_groupNum01 = VAR _lacation = MAX ( 'IssueTable'[Location] ) VAR _issue = MAX ( 'IssueTable'[Issue] ) VAR _issueNumber = MAX ( 'IssueTable'[Issue_Number] ) VAR _result = CALCULATE ( COUNT ( 'IssueTable'[Issue] ), FILTER ( ALL ( IssueTable ), 'IssueTable'[Location] = _lacation && 'IssueTable'[Issue] = _issue ) ) // or use ALLEXCEPT() function RETURN _resultM_maxNum02 = MAXX ( ALLEXCEPT ( IssueTable, IssueTable[Location] ), [M_groupNum01] )Top Issue = CALCULATE ( MAX ( 'IssueTable'[Issue] ), FILTER ( ALLEXCEPT ( 'IssueTable', IssueTable[Location] ), [M_groupNum01] = [M_maxNum02] ) )I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,RichOB .Has your problem been solved?
If you have found suitable solutions, please share them as it will help more users with similar problems.
For example, relevant code or links to articles.
Or you can mark the valid suggestions provided by other users as solutions.
Thank you very much for your understanding and support of Power BI.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
