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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TBoyle
Regular Visitor

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 PBIRelationship set up in PBI

 

 

4 REPLIES 4
v-chuncz-msft
Community Support
Community Support

@TBoyle 

 

You may also apply virtual relationship in DAX measure.

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

I agree with @amitchandak and @edhans . Sample data could help us be more specific. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

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.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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