Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
You may also apply virtual relationship in DAX measure.
https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |