Forum Discussion
TOPn help please
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,
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 mymaxvalueIssues 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.
