Forum Discussion

TBoyle's avatar
TBoyle
Regular Visitor
6 years ago

Calculating the difference between two columns in different tables in PBI

I am trying to calculate the difference between two columns in different tables (Tracking Grade-Target Grade)   TABLE 1 Year 10 Results_current    COLUMN NAME Tracking Grade value   TABLE 2 ...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

This model does not seem right to me you have all many to many bi directional joins.

Please refer https://docs.microsoft.com/en-us/power-bi/guidance/

 

You need to put stuff in a star schema (preferably. Avoid many to Many and Bi-directional join unless most needed and things can not be achieved without that. Create common date, grade dimension and join tables with those.

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    To reiterate what amitchandak is saying, Power BI is a query model and it works best on a Star Schema. See this article. And you want to avoid bi-directional filtering at all costs. 

     

    A key takeaway from that article: "This is the reason we suggest our readers stay away from enabling bidirectional cross-filter relationships in the data model. It is not that bidirectional relationships are bad, or that they are a useless feature. There are a few scenarios where the power of bidirectional cross-filter really shines. However, these need to be leveraged carefully making sure that the model does not become ambiguous because of the bidirectional relationship. Implementing bidirectional cross-filter for the purpose of syncing slicers is definitely a bad idea. After all, if you need to kill ants in the yard, you do not turn on the Death Star."

     

    Create a star schema. This book will really help with data modeling in Power BI. I have no association with SQLBI, they just flat out have some of the best content when it comes to DAX. These two guys have been using SSAS for over 20 years, and the SSAS model is what Power BI is based on. Power BI actually runs a mini-version of the SSAS engine for its DAX model.

     

    As for your formula, it is possible to get it working, but if you make any changes to the model when you add more data, it is very likely to break your formula(s.) Using a Star Schema will help ensure adding new tables doesn't destroy previous work.