Need help with Topn Measure please

[RichOB]

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

[Ashish_Mathur]

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.

[RichOB]

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

[Ashish_Mathur]

You are welcome.  It is a measure

Issue count = counta(data[issue number])

[v-jtian-msft]

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
    _result

M_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.

[Jihwan_Kim]

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 ) )
    )