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

Year 10 Targets_current 

 

COLUMN NAME

Target Grade value

 

I also have a third table holding baseline data which I would also like to calculate the difference with the Tracking Grade in a second calculated column or measure! Then there is a fourth table holding all pupil related data.

 

TABLE 3

MidYIS Year 10_current

 

COLUMN NAME

MidYIS Grade value

 

I had a working measure on Friday but I don't know what has happened because it is no longer giving me the correct results. There are a few more columns I need to create but this needs to be worked out first in order to produce further results. I have posted the relationships as I feel it may have something to do with it.

 

Relationship set up in PBI

 

 

Need Help

4 Replies

  • 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.