RELATEDTABLE DAX with FILTER on GROUP

[someshwarrc]

These are 2 entities in my SSAS model. There can be only 1 unique operation but multiple rounds associated to each operation. 

I want to be able to calculate the number of rounds that only have more than one deviated rounds. 

NOTE: round_deviated is TRUE/FALSE.

[v-xiaotang · Accepted solution]

Hi @someshwarrc 

Thanks for reaching out to us.

please try this measure

Measure = CALCULATE(COUNTROWS(ROUNDS),FILTER(ROUNDS,ROUNDS[operation_id]=MIN(OPERATIONS[operation_id]) && ROUNDS[round_deviated]=TRUE()))

sample data

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You could try

Ops more than 1 deviated round =
SUMX (
    operations,
    VAR numDeviatedRounds =
        CALCULATE (
            COUNTROWS ( RELATEDTABLE ( rounds ) ),
            rounds[round_deviated] = TRUE ()
        )
    RETURN
        IF ( numDeviatedRounds > 1, 1 )
)