Forum Discussion

joepath's avatar
joepath
Helper II
3 years ago
Solved

Summarize stop working with Many to Many relationship

Hello, I have the below measure which was working fine with 1:M relationship, but due to some requirement we need to convert the relationship to M:M. now the measure stop working because its using ...
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    joepath 

    Please try

    Test =
    SUMX (
    CROSSJOIN (
    CROSSJOIN ( VALUES ( user[full_name] ), VALUES ( city[name] ) ),
    VALUES ( country[country code] )
    ),
    VAR M1 = [M1] RETURN IF ( M1 >= 1, M1 )
    )

    but this will make it even slower

tamerj1's avatar
tamerj1
Community Champion
3 years ago

joepath 

Please try

Test =
SUMX (
CROSSJOIN ( VALUES ( user[full_name] ), VALUES ( city[name] ) ),
VAR M1 = [M1] RETURN IF ( M1 >= 1, M1 )
)

 

this will save the FILTER iteration but the price is paid by the IF condition. However, most probably it will be faster. 

joepath's avatar
joepath
Helper II
3 years ago

And third column will be added like this, Crossjoin(values(Full_Name),values(city[name]), Country[code])?

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    joepath 

    Please try

    Test =
    SUMX (
    CROSSJOIN (
    CROSSJOIN ( VALUES ( user[full_name] ), VALUES ( city[name] ) ),
    VALUES ( country[country code] )
    ),
    VAR M1 = [M1] RETURN IF ( M1 >= 1, M1 )
    )

    but this will make it even slower