Outer Join between Dimension tables bypass fact table

[Anonymous]

I have this model and I want to find out the assets without a maintenance plan. So I defined an inactive relationship between Asset and Maintenance Plan tables. I, then, defined this column and it should work but it isn't. What am I missing?

Data Model

 Maint Plan2 = Calculate(Values('Maintenance Plan'[Maintenance Plan]), USERELATIONSHIP(Asset[Asset Number],'Maintenance Plan'[Plan Asset])) 

PS: The PM Forecast is the fact.

[v-xjiin-msft]

Hi @Anonymous,

I'm not quite understand your requirement. Could you please share us some actual data samples with your expected result? So that we'll get a right direction.

By the way, if it is possible please share us your pbix file with One Drive or Google Drive. It'll help us a lot to understand your issue.

Thanks,
Xi Jin.

[Anonymous]

Hi, thanks for looking into this. The model is about forecasting work being generated as maintenance work orders. Suppose you are responsible for the maintenance of machinery and equipment and you have plans to carry them out. You have to schedule these plans so that you can smoothen out your work load. With the model below, it is easy enough to report which asset undergoes maintenance when etc.  The fact in the centre links the asset and the maintenance plans with a date/time, cost , crew etc.

What it does not answer however is: are there assets which are not covered under any maintenance plans?

To answer that, you would have to left join the asset with maintenance plan. I have defined an inactive relationship between the 2 tables and defined the column Maint. plan2 to make use of that relationship, but I don't think that works. 

Sample data

[AlbertoFerrari]

Hi,In your model, you have two issues. First, you are using a bidirectional relationship, which is most of the times a bad choice. Bidirectional filters acts in a very complex way and forecasting their behavior is really hard. I would make it unidirectional, so to have a simpler model.

Then, the rel between assets and maintenance plans need to be - obviously - inactive. The question is "what happens when you activate it?" One of the other relationships needs to be deactivated to avoid ambiguity and you need to find out which one. Just by looking at the picture, I cannot tell, I would need to inspect the model and spend some time on it. As a general rule, relationships between dimensions should be avoided, again to use a simpler model.

You can achieve the same behavior with TREATAS, controlling by code how the filters are moved and maintaining a set of non-ambiguous relationships.

I am currently writing an article about it, unfortunately it is not yet ready ;( But the conclusion is a very simple one: avoid bidirectional filters and inactive relationships between dimensions, unless you really love to spend days trying to figure out why the numbers are wrong 🙂

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[Anonymous]

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? 

[AlbertoFerrari · Accepted solution]

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