Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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...
Solved! Go to Solution.
@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 )
@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 )
@Anonymous
Please see the file attached as well
@Zubair_Muhammad,
Thanks so much for your help. I've been side-tracked with other projects, but will be sure to get back to you on this.
Hi @Anonymous
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |