Forum Discussion

joepath's avatar
joepath
Icon for Helper II rankHelper 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 SUMMARIZE function, and SUMMARIZECOLUMS is not working because I need to use it in tabular report.

How to convert this meausure so it can work with M:M relationship giving the same result in optimize way.

 

Test = SUMX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(Fact,user[full_name],city[name]),
"@M1",[M1]

),
[@M1]>=1
)
,[@M1]
)

 

Thanks,

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

11 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi joepath 
    Please try

    Test =
SUMX (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( user[full_name] ), VALUES ( city[name] ) ),
            "@M1", [M1]
        ),
        [@M1] >= 1
    ),
    [@M1]
)
    • joepath's avatar
      joepath
      Icon for Helper II rankHelper II
      3 years ago

      Thanks tamerj1 for looking into this, seems working but performance is very slow can we do somthing about it?

      And I have 3 col so should do like Crossjoin(values(Full_Name),values(city[name]), Country[code]) ?

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity 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.