DAX Many to Many

[Poovarasan]

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&month'>=Table2'Year&Month',Table1'Rate',0)

I have tried to use RELATED function here but not helping for this senario

[NidhiBhusari]

Hi @Poovarasan try changing the relationship between the two table to Many - To - One.

[Poovarasan]

Hi, @NidhiBhusari  

Yeah, I tried but both table ID column has duplicate value when i try to change it Power BI will not accept Many-To-One relationship

Thanks 

Poovarasan

[NidhiBhusari]

@Poovarasan Try to create a bridging table between two tables and then set relationship many - to - one.
or can you share sample file? Let me try once

[Poovarasan]

Hi, @NidhiBhusari 
Yeah, Sure 
Unable to attach file here. I have uploaded in OneDrive.
Please find pbi file in below link,
https://swiftant0-my.sharepoint.com/:u:/g/personal/sapowerbi_swiftant_com/EYTINAUftwVPpoYpeM0Ny-8Bjw...

Thanks 
Poovarasan

[NidhiBhusari]

Hi @Poovarasan , please use the link attached to access the file with solution - https://nicesoftwaresolutions1-my.sharepoint.com/:u:/g/personal/nbhusari_nicesoftwaresolutions_com/E...

[Samarth_18]

Hi @Poovarasan ,

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

[Poovarasan]

Hi, @Samarth_18 & @NidhiBhusari  ,

Here is my excepted output.

Thanks
Poovarasan

[Samarth_18]

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:-

[Poovarasan]

Hello @Samarth_18,

Yes, Your correct

Thanks

Poovarasan

[Samarth_18]

Hi @Poovarasan ,

Your relation between both table is with ID colum right?

Thanks,

Samarth