Forum Discussion
Outer Join between Dimension tables bypass fact table
If you activate the inactive relationship, then there will be two paths from Asset to the PM fact table: one direct and one through the maintenance plan. As such, the model will be ambiguous and Power BI does not work with ambiguous models. One of the other relationships need to be deactivated to perform any calculation.
The engine cannot deactivate the one you are activating with USERELATIONSHIP and it will not deactivate the direct one. Instead, most likely, it will deactivate the relationship between Maintenance plan and the PM table. As such, any filter applied to the Asset will filter the fact table with the direct relationship, vanishing your goal of using the maintenance plan as a bridge.
Instead, you can move the filter manually using TREATAS like in this code:
FilterMaintenanceByPlan := CALCULATE ( ..., TRETAS ( VALUES ( Asset[AssetKey] ), MaintenancePlan[AssetKey] ) )Nevertheless, if every maintenanceplan has an asset key, then it would be much better to model everything iwth a simpler snowflake, where the rel between maintenanceplan and asset is always active and you kill the rel between asset and the PM fact table.
The thing is: when you create a relationship between two dimensions, this is a clear signal that the model is the wrong one, and you need to revisit it trying to make it a star schema.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
HI, thanks for looking into this. The community is really active, love it.
I was testing something unrelated with the bidirectional relationship and can get rid of it if it has a bearing on the solution. I don't think the model is particularly complex. The 2 dimensions can be joined directly using 'asset ID'. Agree that a join between dimensions should be avoided but this is a special case and that is why I am using DAX . I am also not using any other table apart from the 2 dimension tables (to answer this question of assets without plans).
I don't understand why the custom column won't work for assets without plans. It is a simple enough expression, use a relationship to filter and give a list of values and it works fine when the assets have plans.
Ignoring all other tables, what should be the DAX expression to achieve a left outer join?
Most Valuable ProfessionalIf you activate the inactive relationship, then there will be two paths from Asset to the PM fact table: one direct and one through the maintenance plan. As such, the model will be ambiguous and Power BI does not work with ambiguous models. One of the other relationships need to be deactivated to perform any calculation.
The engine cannot deactivate the one you are activating with USERELATIONSHIP and it will not deactivate the direct one. Instead, most likely, it will deactivate the relationship between Maintenance plan and the PM table. As such, any filter applied to the Asset will filter the fact table with the direct relationship, vanishing your goal of using the maintenance plan as a bridge.
Instead, you can move the filter manually using TREATAS like in this code:
FilterMaintenanceByPlan :=
CALCULATE (
...,
TRETAS (
VALUES ( Asset[AssetKey] ),
MaintenancePlan[AssetKey]
)
)Nevertheless, if every maintenanceplan has an asset key, then it would be much better to model everything iwth a simpler snowflake, where the rel between maintenanceplan and asset is always active and you kill the rel between asset and the PM fact table.
The thing is: when you create a relationship between two dimensions, this is a clear signal that the model is the wrong one, and you need to revisit it trying to make it a star schema.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com