Help with USERELATIONSHIP and Many to Many

Question

Hello Fabric Community,&nbsp;I'm trying to solve this DAX/Model problem, here's the scenario.&nbsp;I have a dimension table with an event called "original sailing key" and "mapped saling key", the problem I have is the "mapped" key has duplicate values so my inactive relationship is a many-to-many type.&nbsp;Measure:Revenue (Mapped) =CALCULATE([Revenue],&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP('Passenger'[Ship Sddt], Sailings[Mapped Sailing Key]))&nbsp;This is causing my measure "Revenue (Mapped)" to work incorrectly with the duplicate values.&nbsp;&nbsp;How can I adjust my measure to work around the duplicate values and bringing in the right revenue amount?&nbsp;Data model:&nbsp;

anonymous · Answer

Hi esulyma&nbsp;,
Based on the description, try to create a bridge table and the table stores the unique sailing key.
Then, create the relationship between the bridge table and passenger table, sailings table.
What’s more, try to modify the measure to the following dax formula.
Revenue (Mapped) =
CALCULATE(
    [Revenue],
    USERELATIONSHIP('BridgeTable'[Sailing Key], 'Sailings'[Mapped Sailing Key])
)
You can also view the following documents to learn more information.
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
Many-to-many relationship guidance - Power BI | Microsoft Learn
&nbsp;
Best Regards,
Wisdom Wu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.