Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

Outer Join between Dimension tables bypass fact table

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 ModelData Model
 Maint Plan2 = Calculate(Values('Maintenance Plan'[Maintenance Plan]), USERELATIONSHIP(Asset[Asset Number],'Maintenance Plan'[Plan Asset])) 
 
PS: The PM Forecast is the fact.
1 ACCEPTED SOLUTION
AlbertoFerrari
Most Valuable Professional
Most Valuable Professional

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

Alberto Ferrari - SQLBI

View solution in original post

5 REPLIES 5
v-xjiin-msft
Solution Sage
Solution Sage

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
Not applicable

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 dataSample data

AlbertoFerrari
Most Valuable Professional
Most Valuable Professional

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

Alberto Ferrari - SQLBI
Anonymous
Not applicable

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
Most Valuable Professional
Most Valuable Professional

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

Alberto Ferrari - SQLBI

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.