Forum Discussion

Poovarasan's avatar
Poovarasan
Frequent Visitor
5 years ago

DAX Many to Many

I have a two tables with Many-To-Many relationship, Here's the screenshot for two tables Table1: Table2: I need to create measure like this  Sum of rate = Sumx(Table1,IF(Table1'Year...
Power BI Dax
Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Poovarasan ,

Is this the expected output you are looking for, if not then could you please share expected output as well?

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Poovarasan , 

     

    Based on provided data total sum of rate is 149.56, how it could be 333.68, if the output which you have provided is not based on provided data then you can try below code:-

    Sum of Rate =
VAR result =
    CALCULATE (
        SUM ( 'Table1'[Rate] ),
        FILTER ( Table2, Table2[Year&Month] <= MAX ( Table1[Year&Month] ) )
    )
RETURN
    IF ( result = 0, 0, result )

    Output:-