topic Re: SUMMARIZE two tables many to many Relationship in DAX Commands and Tips

SUMMARIZE two tables many to many Relationship

r_fioretti — Mon, 22 Jan 2024 19:57:12 GMT

I'm trying to get this in Excel (Edit DAX in Table) and I need the column 'table2'[DesPro], but I can't figure it out how to do that.

So far, I have:

FILTER(

SUMMARIZE(

table1,

table1[NF Saída],

table1[Título],

table1[Total],

table1[Ult. Pgto],

'table2'[DesPro] -- where table1[NF Saída]='table2'[Nota Fiscal] with many to many relationship. A union table with all distinct values from both tables had been created

table1[Ult. Pgto] <> BLANK()&&table1[Ult. Pgto] >= DATE(2023, 1, 1)

)

I appreciate any help.

Re: SUMMARIZE two tables many to many Relationship

Anonymous — Tue, 23 Jan 2024 01:57:54 GMT

Hi r_fioretti ,

Please try:

Table= FILTER( SUMMARIZE( table1, table1[NF Saída], table1[Título], table1[Total], table1[Ult. Pgto], "DesPro",MAX('Table2'[DesPro]) ), table1[Ult. Pgto] <> BLANK()&&table1[Ult. Pgto] >= DATE(2023, 1, 1) )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

Re: SUMMARIZE two tables many to many Relationship

r_fioretti — Tue, 23 Jan 2024 14:00:14 GMT

Hi,
I'm using Excel and It didn't work. It brings just the MAX value. Instead, I need to bring all values.
I don’t know why but I can’t attach an example file. Here’s a link to access it. SUMMARIZE-Ex.xlsx
I appreciate.

Re: SUMMARIZE two tables many to many Relationship

Anonymous — Wed, 24 Jan 2024 07:53:05 GMT

Hi r_fioretti ,

Model Relationship:

Please try:

Table = FILTER( SUMMARIZE( table1, table1[NF Saída], table1[Título], table1[Total], table1[Ult. Pgto], "DesPro",LOOKUPVALUE('Table2'[DesPro],'Table2'[Nota Fiscal],'Table1'[NF Saída]) ), table1[Ult. Pgto] <> BLANK()&&table1[Ult. Pgto] >= DATE(2023, 1, 1) )

Or:

Table = FILTER( SUMMARIZE( table1, table1[NF Saída], table1[Título], table1[Total], table1[Ult. Pgto], "DesPro",CALCULATE(SUM('Table2'[DesPro]),FILTER(ALL('Table2'),'Table2'[Nota Fiscal]=EARLIER('Table1'[NF Saída]))) ), table1[Ult. Pgto] <> BLANK()&&table1[Ult. Pgto] >= DATE(2023, 1, 1) )

Best Regards,

Neeko Tang

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

Re: SUMMARIZE two tables many to many Relationship

r_fioretti — Thu, 25 Jan 2024 12:41:16 GMT

Hi,
It didn’t work.

In Power BI the relationship many to many works well. Unfortunately, I need this model in Excel to meet the final user needs. I have an intermediary table with unique values as a work around to this many to many relationships, it should work but it isn’t.