Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count where 'Model Classification' <> 'Aggregate Classifcation'

Community,

 

Need help with creating a measure.

 

My data looks like this:

 

IDAnalystStart TimeEnd TimeClassificationDurationEventPK_WID_EID
507Aggregate3/27/2019 12:003/29/2019 4:00Line Pressure1.666666667Event 1507_Event_1
507Model3/27/2019 14:453/29/2019 17:40Line Pressure2.121527778Event 1507_Event_1
507Aggregate4/4/2019 15:154/5/2019 13:45Line Pressure0.9375Event 3507_Event_3
507Model4/4/2019 19:004/5/2019 23:40Line Pressure1.194444444Event 3507_Event_3
507Aggregate3/31/2019 6:154/2/2019 12:05AD - Tubing2.243055556Event 2507_Event_2
507Model3/31/2019 22:004/2/2019 12:15Anomaly Detected1.59375Event 2507_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...

1 ACCEPTED 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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@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
    )
)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad 

 

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 )

Regards
Zubair

Please try my custom visuals

@Anonymous 

 

Please see the file attached as well

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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

Anonymous
Not applicable

@v-juanli-msft,

 

This is solved.  Thanks.  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.