topic Re: Summarize stop working with Many to Many relationship in DAX Commands and Tips

Summarize stop working with Many to Many relationship

joepath — Sun, 08 Jan 2023 08:27:58 GMT

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,

Re: Summarize stop working with Many to Many relationship

tamerj1 — Sun, 08 Jan 2023 15:12:36 GMT

Hi joepath
Please try

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

Re: Summarize stop working with Many to Many relationship

joepath — Sun, 08 Jan 2023 18:44:21 GMT

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]) ?

Re: Summarize stop working with Many to Many relationship

tamerj1 — Sun, 08 Jan 2023 19:31:10 GMT

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.

Re: Summarize stop working with Many to Many relationship

joepath — Sun, 08 Jan 2023 19:49:16 GMT

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

Re: Summarize stop working with Many to Many relationship

tamerj1 — Sun, 08 Jan 2023 19:52:53 GMT

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

Re: Summarize stop working with Many to Many relationship

joepath — Mon, 09 Jan 2023 10:28:34 GMT

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

Re: Summarize stop working with Many to Many relationship

tamerj1 — Mon, 09 Jan 2023 11:45:13 GMT

joepath
What is the DAX for [M1] measure?

Re: Summarize stop working with Many to Many relationship

joepath — Mon, 09 Jan 2023 11:54:40 GMT

Its a multiplication of two measure from the Fact table.

Re: Summarize stop working with Many to Many relationship

tamerj1 — Mon, 09 Jan 2023 12:31:46 GMT

joepath
And what is the dax fro the two measures?

Re: Summarize stop working with Many to Many relationship

joepath — Mon, 09 Jan 2023 15:12:46 GMT

its simple sum.

Re: Summarize stop working with Many to Many relationship

tamerj1 — Mon, 09 Jan 2023 16:39:11 GMT

joepath

Hmm 🤔

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