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

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

Reply
smcknezie04
Frequent Visitor

Related function returns unexpected results

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 AllocatedTotal HoursHours RemainingHours Allocated Check
3,6212,7633,089,2063,091,969
1,032795359,530360,325
958703263,774264,478

 

 

8 REPLIES 8
smcknezie04
Frequent Visitor

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

 

smcknezie04_0-1698766165042.png

 

smcknezie04_1-1698766421753.png

 

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?

DataestDog_0-1698767997228.png


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.

Data-estDog
Resolver II
Resolver II

What is the relationship between the two tables? Model view screen shot?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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