Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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 ) ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 ) ) ) )
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |