TOPn help 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
Manchester	Aggression
Manchester	Aggression
Manchester	Aggression
Manchester	Aggression
Manchester	Neglect
Manchester	Neglect
Edinburgh	Neglect
Edinburgh	Violence
Edinburgh	Violence
Edinburgh	Violence
Edinburgh	Violence
Edinburgh	Neglect
Newcastle	Emotional
Newcastle	Violence
Newcastle	Aggression
Newcastle	Emotional
Newcastle	Emotional
Newcastle	Emotional

Table2

Edinburgh	Violence	4
Manchester	Aggression	4
Newcastle	Neglect	4

Thanks

[speedramps · Accepted solution]

I have seen almost excatly the same question posted with same data over the last few days, and assume you are a from class of students who have been given the same exam homework.  I hope you are not cheating asking for help 🙄. 

The question contains a trick. What happens if a locations have mote than one issues which have the top number of incidents.

For example in this data London has 5 incidents of Theft and Drugs

Location	Issue	Incident
Manchester	Aggression	Incident 1
Manchester	Aggression	Incident 2
Manchester	Aggression	Incident 3
Manchester	Aggression	Incident 4
Manchester	Neglect	Incident 5
Manchester	Neglect	Incident 6
Edinburgh	Neglect	Incident 7
Edinburgh	Violence	Incident 8
Edinburgh	Violence	Incident 9
Edinburgh	Violence	Incident 10
Edinburgh	Violence	Incident 11
Edinburgh	Neglect	Incident 12
Newcastle	Emotional	Incident 13
Newcastle	Violence	Incident 14
Newcastle	Aggression	Incident 15
Newcastle	Emotional	Incident 16
Newcastle	Emotional	Incident 17
Newcastle	Emotional	Incident 18
London	Theft	Incident 19
London	Theft	Incident 20
London	Theft	Incident 21
London	Theft	Incident 22
London	Theft	Incident 23
London	Drugs	Incident 24
London	Drugs	Incident 25
London	Drugs	Incident 26
London	Drugs	Incident 27
London	Drugs	Incident 28
Manchester	Theft	Incident 29
Edinburgh	Theft	Incident 30
Newcastle	Theft	Incident 31
Manchester	Theft	Incident 32
Edinburgh	Theft	Incident 33
Newcastle	Drugs	Incident 34
Manchester	Drugs	Incident 35
Manchester	Drugs	Incident 36
Newcastle	Drugs	Incident 37

Click here to download my PBIX solution form Onedrive.

Please click thumbs up for the helpful suggestion

and click accept solution if it works.  You can accept multiple solutions in the thread,

Click here 

How it works

Incidents = 
// get the number of incidents for the context
COUNTROWS(Facts)

Highest by location = 
VAR mylocation = SELECTEDVALUE(Facts[Location])
VAR mymaxvalue = 
MAXX(
    VALUES(Facts[Issue]),
    [Incidents]
)
RETURN
mymaxvalue

Issues with max = 
// get the current context location
VAR myissue = SELECTEDVALUE(Facts[Location])

// get the maxium number of incidents by issue for the current location
VAR mymaxvalue = 
MAXX(
    VALUES(Facts[Issue]),
    [Incidents]
)

// create a temp table of issues (just for the context location) that equal the max value
VAR mymaxissues =
FILTER(VALUES(Facts[Issue]), [Incidents] = mymaxvalue)

RETURN
// delimit
CONCATENATEX(mymaxissues,Facts[Issue], " ,")

If you download the PBIX you will see there are also measures to get the loactions with highest incident rate for each type of issue.

[ryan_mayu]

@RichOB 

you can try this

Measure = rankx(FILTER(all('Table'),'Table'[Location]=max('Table'[Location])),[count],,DESC)

and add this measure to visual filter and set to 1

pls see the attachment below

[AnushaSri]

Hi @RichOB , You can achieve this by the feature available in power BI filters section, where you can follow the steps

1.Select table you want to apply Top N filter

2. go to filters. 

3. In your case select Location

4.Select filter type to Top N

5. Set the number of top rows you want to see on the table.

6.select the column/measure on what basis you want to filter location for Top N, in your case it seems count of issues.

Hope it helps, Please mark it as a solution accepted, if it helps to resolve your issue.

[amitchandak]

@RichOB , First create a meausre say 

M1 = countrows(Table1) 

Then create another measure for top 3 location

calculate([M1], keepfilters(TOPN(3, allselected(Table[Location]), [M1], Desc) ) )

use that in visual with location

Also check Window function

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ