Summarize stop working with Many to Many relationship

[joepath]

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,

[tamerj1]

Hi @joepath 
Please try

Test =
SUMX (
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( VALUES ( user[full_name] ), VALUES ( city[name] ) ),
            "@M1", [M1]
        ),
        [@M1] >= 1
    ),
    [@M1]
)

[joepath]

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]

@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]

Tried this one, still taking lot of time to execute.

[tamerj1]

@joepath 
What is the DAX for [M1] measure?

[joepath]

Its a multiplication of two measure from the Fact table.

[tamerj1]

@joepath 
And what is the dax fro the two measures?

[joepath]

its simple sum.

[tamerj1]

@joepath 

Hmm 🤔 

can you please share a screenshot of your data model with relationships and advise which columns that are being summed. 

[joepath]

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

[tamerj1 · Accepted solution]

@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