Counting dimension value when equal to max of another dimension.

[stascher]

Given the following table:

Subject	Event	Severity
Bob	fall	1
Bob	fall	2
Bob	fall	1
Jan	fall	3
Jan	cut	2
Jan	break	3
Jan	break	2
Stew	cut	1
Stew	cut	1
Stew	break	3

I want to create a stacked bar chart where Event is one dimension, Severity is another

and the measure is a distinct count of Subjects experiencing each Event for a given Severity,

but ONLY if the Severity is the maximum severity that Subject ever experienced for the given Event.

I tried count( distinct aggr( if( max( Severity)=Severity, Subject),Subject))    but obviously it didn't work.

Thanks for any help,

Steve

[sunny_talwar]

How about this

Count(DISTINCT Aggr(If(Max(TOTAL <Event, Subject> Severity) = Severity, Subject), Subject, Event, Severity))

[sunny_talwar · Accepted solution]

This is what I get

[stascher]

Knew I could count on you Sunny. Thanks much!