SUMMARIZE two tables many to many Relationship

[r_fioretti]

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.

[v-tangjie-msft]

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. 

[r_fioretti]

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.

[v-tangjie-msft]

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. 

[r_fioretti]

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.