Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Tackle Many to many relationship ?

Hello everyone,

 

I'm working on a tableau migration, and in tableau I have one report to migrate, in this report, the following measure is create:

 

sum([Threshold Value]*[Weight])/SUM(IIF(ISNULL([Value]),0,[Weight]))

 

So when you see this measure, you think it will be an easy one, but once you start to have a look on the model behind, the issue starts to come...

This is the model (I rebuilt in Power BI to get something more clear)

 

So the first part of the formula was pretty easy to build; I just use the bridge table in this table I add the following calculated column: 

RELATED(tab_threshold[ThresholdScore]) * RELATED(tab_weight[weight]) (it is also working for a measure)
 
But the issue is comming from the rest of the formula, for the relationship between the weight and the indicator, I'm not able to build a bridge table (without to much complexity), so I could not use the same workaround
 
Any idea of what I can do?
 
I try also to build a big table by making some joins, but I have to much rows and my calculation is returning worng values...
 
Many thanks for your help 🙂
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    The RELATED function only works in many-to-one or one-to-one relationships because it is designed to return a single value. In a many-to-many relationship, there may be multiple related values and it is impossible to determine which value to return.

     

    Since you created the relationship, you can calculate it directly without creating a calculated column by creating a measure, you can write it as:

    DIVIDE (
    SUM ( 'BridgeTable'[the calculated column'] ) * SUM ( 'tab_weight'[weight] ),
    IF (
        MAX ( 'tab_indicators'[value] ) = BLANK (),
        0,
        SUM ( 'tab_weight'[weight] )
    )
)

     

    If the problem persists, can you provide .pbix file with sensitive data removed or a portion of each table to help the problem be resolved more quickly?

     

    Best Regards,
    Zhu
    Community Support Team

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    The RELATED function only works in many-to-one or one-to-one relationships because it is designed to return a single value. In a many-to-many relationship, there may be multiple related values and it is impossible to determine which value to return.

     

    Since you created the relationship, you can calculate it directly without creating a calculated column by creating a measure, you can write it as:

    DIVIDE (
    SUM ( 'BridgeTable'[the calculated column'] ) * SUM ( 'tab_weight'[weight] ),
    IF (
        MAX ( 'tab_indicators'[value] ) = BLANK (),
        0,
        SUM ( 'tab_weight'[weight] )
    )
)

     

    If the problem persists, can you provide .pbix file with sensitive data removed or a portion of each table to help the problem be resolved more quickly?

     

    Best Regards,
    Zhu
    Community Support Team

     

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