Hi
I have a table of events and event scores. Dummy file attached.
I am trying to create a table which will show aggregate scores for PersonIDs for a given time period.
A person can have several Instance_IDs. I only want to select one instance_ID per personID. The logic for this is to count the most Event with the maximum seriousness. If there is more than one event with the same seriousness, I want to count the most recent Instance_ID by start date.
I have written a measure for this logic and have tested each stage individually. It seems to work. However, I am struggling to then use this to aggregate the scores.
Can anyone help. I need to use this methodology for a number of measures. I can't do this as a calculated column as the date range used needs to be dynamic - sometimes based on a whole year, sometimes quarters.
Thank you
Here is my dax code that I essentially need to use to filter the table:
MaxSeriousness =
VAR CountEvents = CALCULATE(
COUNTROWS('Events'),
ALLEXCEPT('Events', 'Events'[PersonID])
)
VAR MaxSeriousness = CALCULATE(
MAX('Events'[Seriousness]),
ALLEXCEPT('Events', 'Events'[PersonID])
)
VAR CountMostSerious = CALCULATE(
COUNTROWS('Events'),
ALLEXCEPT('Events', Events[PersonID]),
Events[Seriousness] = MaxSeriousness
)
VAR MaxStartDateMostSerious = CALCULATE(
MAX('Events'[START_DATE]),
ALLEXCEPT('Events', Events[PersonID]),
Events[Seriousness] = MaxSeriousness
)
Var Result = IF(
CountEvents = 1 ||
(CountMostSerious = 1 && MIN('Events'[Seriousness]) = MaxSeriousness) ||
(MIN('Events'[Seriousness]) = MaxSeriousness && MIN('Events'[START_DATE]) = MaxStartDateMostSerious),
1,
0
)
RETURN
Result
https://url.uk.m.mimecastprotect.com/s/l9H4CZVyXHQD32KHzjqa4?domain=1drv.ms
