Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I'm trying to find the remaining hours available (Hours Remaining) by subtracting "Total Hours" in the CDTLaborView table from "Total Hours Allocated" in the Financials table using the following:
Hours Remaining = Related(Financials[Total Hours Allocated]) - CDTLaborView[Total Hours]
The results were not as expected (see below) so I tried using just the related function (Hours Allocated Check) and realized its not working the way I expected.
What am I doing wrong, whats the fix or is there a better way?
Total Hours Allocated | Total Hours | Hours Remaining | Hours Allocated Check |
3,621 | 2,763 | 3,089,206 | 3,091,969 |
1,032 | 795 | 359,530 | 360,325 |
958 | 703 | 263,774 | 264,478 |
@Data-estDog The relationship is Many to One, CDTLaborView is many and Financials is One.
I am going to need more information/context here. A 1 to * type doesn't tell me much.
This post give some great tips on how to present your situation so we can best help: How to Get Your Question Answered Quickly - Microsoft Fabric Community
They are linked via WBSID which is a combination of the WBS# fields so we could create a relationship between the tables.
This is a very odd model. Financials appears to be a partial dimension linked to multiple fact tables. Actually this looks like there has been no modeling done, all the tables are flattend and linked with transactional source system keys. I am inclined to think the relationships are exploding the results somehow.
In your output example, what are the rest of the fields? What tables are they coming from?
Also, in your calculation you use: CDTLaborView[Total Hours]. Different than the "Flat CDTLaborView". Did you just simplify the table name for the example or is there another table somewhere?
To the left are things like Project Manager, Dept, Project # that I didn't think was relevent.
Majority of it is coming from "Flat CDTLaborView" with only whats necessary coming from "Financials".
Yep, I excluded the "flat" part for simplicity and didn't know it meant anything.
So it is not columns except from the two tables in question.
I can't seem to reproduce the error.
Short of digging around in the pbix file myself, not sure what I can do. If you are inclide to post a link to a pbix file (idealy one that uses fake data, but still in the same format etc.) I would spend a little bit looking at it for you.
Thanksf or offering and I'll see if I can get a sanatized version together later this week.
What is the relationship between the two tables? Model view screen shot?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |