Forum Discussion
Count where 'Model Classification' <> 'Aggregate Classifcation'
Community,
Need help with creating a measure.
My data looks like this:
| ID | Analyst | Start Time | End Time | Classification | Duration | Event | PK_WID_EID |
| 507 | Aggregate | 3/27/2019 12:00 | 3/29/2019 4:00 | Line Pressure | 1.666666667 | Event 1 | 507_Event_1 |
| 507 | Model | 3/27/2019 14:45 | 3/29/2019 17:40 | Line Pressure | 2.121527778 | Event 1 | 507_Event_1 |
| 507 | Aggregate | 4/4/2019 15:15 | 4/5/2019 13:45 | Line Pressure | 0.9375 | Event 3 | 507_Event_3 |
| 507 | Model | 4/4/2019 19:00 | 4/5/2019 23:40 | Line Pressure | 1.194444444 | Event 3 | 507_Event_3 |
| 507 | Aggregate | 3/31/2019 6:15 | 4/2/2019 12:05 | AD - Tubing | 2.243055556 | Event 2 | 507_Event_2 |
| 507 | Model | 3/31/2019 22:00 | 4/2/2019 12:15 | Anomaly Detected | 1.59375 | Event 2 | 507_Event_2 |
I need a distinct count of events foreach ID where the 'Classification' values don't match.
So in the example above the measure would return a count of (1) since there was one event with a classifciation mismatch (e.g. 'Event 2')
I've tried using GROUPBY() and SUMMARIZE() functions without any luck...
Anonymous
In that case try
Measure = VAR myevents = COUNTX ( VALUES ( Table1[Event] ), IF ( CALCULATE ( MAX ( Table1[Classification] ), Table1[Analyst] = "Aggregate" ) <> CALCULATE ( MAX ( Table1[Classification] ), Table1[Analyst] = "Model" ), 1 ) ) VAR allevents = COUNTROWS ( VALUES ( Table1[Event] ) ) RETURN DIVIDE ( myevents, allevents )
7 Replies
Anonymous
Try this MEASURE
Measure = COUNTX ( VALUES ( Table1[Event] ), IF ( CALCULATE ( MAX ( Table1[Classification] ), Table1[Analyst] = "Aggregate" ) <> CALCULATE ( MAX ( Table1[Classification] ), Table1[Analyst] = "Model" ), 1 ) )Thanks so much for your reply. Your measure works. I didn't explain well what I'm needing.
I want to see the percent of events where the classifications don't match.
[Count of Events where 'Model Classification' <> 'Aggregate Classification'] / [Total Events]
Anonymous
In that case try
Measure = VAR myevents = COUNTX ( VALUES ( Table1[Event] ), IF ( CALCULATE ( MAX ( Table1[Classification] ), Table1[Analyst] = "Aggregate" ) <> CALCULATE ( MAX ( Table1[Classification] ), Table1[Analyst] = "Model" ), 1 ) ) VAR allevents = COUNTROWS ( VALUES ( Table1[Event] ) ) RETURN DIVIDE ( myevents, allevents )