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
Blinding1
Regular Visitor

Help on writing measure for CountX or SumX or Calculate on Groups

Hi all, first post for me. I hope it's explained clearly enough. 

 

I am having trouble writing a measure which will give me the number of times a person was quicker than another at a task.

 

Scenario: Three people had four attempts each at a task. The time taken for the tasks are recorded. The 'results' table is as follows:

 

AttemptPersonTime in Seconds
1Person A60
1Person B61
1Person C62
2Person A50
2Person B49
2Person C48
3Person A40
3Person B40
3Person C40
4Person A31
4Person B33
4Person C32


I am trying to summarise the number of times a person was faster than another and get an output table like the following:

 

CombinationsNo. of Times Left Person Beat Right Person
Person A : Person B2
Person A : Person C2
Person B : Person A1
Person B : Person C1
Person C : Person A1
Person C : Person B2


I have no problem constructing in powerquery (if need be) a single column lookup table of attempt numbers (1 to 4), or a table with the combinations such as below. 

 

ConcatPerson on LeftPerson on Right
Person A : Person BPerson APerson B
Person A : Person CPerson APerson C
Person B : Person APerson BPerson A
Person B : Person CPerson BPerson C
Person C : Person APerson CPerson A
Person C : Person BPerson CPerson B


I was trying to countx through the attempts to add up when "person on left" was quicker than "person on right" but I just could not get it to work. I also tried a Sumx on a "if quicker then 1 else 0" but couldnt get that to work either.

 

Thanks all.

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

Hello @Blinding1 

 

you can build the below model (inactive relationships) and add this measure in a 'Table Visual'

 

Measure = 
IF(
    HASONEVALUE( Persons1[Person] ) && HASONEVALUE( Persons2[Person] ),
    IF( 
        VALUES( Persons1[Person] ) <> VALUES( Persons2[Person] ),
        SUMX(
            VALUES( Attempts[Attempt] ),
            IF( 
                CALCULATE( 
                    VALUES( Attempts[Time in Seconds] ), 
                    USERELATIONSHIP( Attempts[Person], Persons1[Person] )
                ) < 
                CALCULATE( 
                    VALUES( Attempts[Time in Seconds] ), 
                    USERELATIONSHIP( Attempts[Person], Persons2[Person] )
                ), 
                1, 0
            )
        )
    )
)

 

 

 

 

 

2019-03-11_14-58-36.jpg2019-03-11_14-58-20.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

2 REPLIES 2
LivioLanzo
Solution Sage
Solution Sage

Hello @Blinding1 

 

you can build the below model (inactive relationships) and add this measure in a 'Table Visual'

 

Measure = 
IF(
    HASONEVALUE( Persons1[Person] ) && HASONEVALUE( Persons2[Person] ),
    IF( 
        VALUES( Persons1[Person] ) <> VALUES( Persons2[Person] ),
        SUMX(
            VALUES( Attempts[Attempt] ),
            IF( 
                CALCULATE( 
                    VALUES( Attempts[Time in Seconds] ), 
                    USERELATIONSHIP( Attempts[Person], Persons1[Person] )
                ) < 
                CALCULATE( 
                    VALUES( Attempts[Time in Seconds] ), 
                    USERELATIONSHIP( Attempts[Person], Persons2[Person] )
                ), 
                1, 0
            )
        )
    )
)

 

 

 

 

 

2019-03-11_14-58-36.jpg2019-03-11_14-58-20.jpg

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thanks for this solution. It's amazing, and the measure is clean and straightforwardly written. Thanks so much.

 

So cool

 

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.