Help with USERELATIONSHIP and Many to Many

[esulyma]

Hello Fabric Community,

I'm trying to solve this DAX/Model problem, here's the scenario.

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.

Measure:

Revenue (Mapped) =

CALCULATE([Revenue],

        USERELATIONSHIP('Passenger'[Ship Sddt], Sailings[Mapped Sailing Key])

)

This is causing my measure "Revenue (Mapped)" to work incorrectly with the duplicate values.

How can I adjust my measure to work around the duplicate values and bringing in the right revenue amount?

Data model:

[v-jiewu-msft]

Hi @esulyma ,

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

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[esulyma]

Hello! Thanks for your reply, I tried this before without any sucess.

I gave it another shot with your guidance, below are the changes I made where it shows the unexpected results.

Data model:

Visual view where the Revenue (Mapped) measure doesn't show the expected value for mapped (starting in $9):

Am I doing something wrong?