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

[Blinding1]

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:

Attempt	Person	Time in Seconds
1	Person A	60
1	Person B	61
1	Person C	62
2	Person A	50
2	Person B	49
2	Person C	48
3	Person A	40
3	Person B	40
3	Person C	40
4	Person A	31
4	Person B	33
4	Person C	32

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

Combinations	No. of Times Left Person Beat Right Person
Person A : Person B	2
Person A : Person C	2
Person B : Person A	1
Person B : Person C	1
Person C : Person A	1
Person C : Person B	2

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. 

Concat	Person on Left	Person on Right
Person A : Person B	Person A	Person B
Person A : Person C	Person A	Person C
Person B : Person A	Person B	Person A
Person B : Person C	Person B	Person C
Person C : Person A	Person C	Person A
Person C : Person B	Person C	Person 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.

[LivioLanzo · Accepted solution]

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

[Blinding1]

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

So cool