cancel
Showing results for
Did you mean:
Highlighted
Frequent 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:

 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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

Proud to be a Datanaut!

2 REPLIES 2
Super User

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

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

Proud to be a Datanaut!

Frequent Visitor

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

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

So cool