Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Poovarasan try changing the relationship between the two table to Many - To - One.
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
@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
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
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...
Hi @Poovarasan ,
Is this the expected output you are looking for, if not then could you please share expected output as well?
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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:-
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Poovarasan ,
Your relation between both table is with ID colum right?
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
73 | |
50 | |
45 | |
20 | |
17 |