cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User IV
Super User IV

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors