Hierarchical Count

[Harry41]

Hi all,

I'm trying to create a chart (either donut or 100% stacked bar) to show the most severe level incident for a case. I have 2 tables, one containing a caseID and basic case info, and another containing an incidentID, the caseID it relates to and basic incident info. The issue is the fact that 1 case often has more than one incident, with differing severity (Minor, Major, Severe), with some cases having 0 incidents.

I've tried making a new column in the case table, which looks in the incidentID table for Severe events, then Major, then Minor, but I cannot figure out how to get this to work.

Any ideas?

[amitchandak · Accepted solution]

@Harry41 , Based on what I got

new column in case table =
var _1 = maxx(filter(events,events[Case ID] = Case[Case ID] && [severity] = "Severe"), [incidentID])
var _2 = maxx(filter(events,events[Case ID] = Case[Case ID] && [severity] = "Major"), [incidentID])
var _3 = maxx(filter(events,events[Case ID] = Case[Case ID] && [severity] = "Minor"), [incidentID])
return
coalesce(_1,_2,_3)