Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |