PBI will not sum hours correctly

[croberts21]

I have PBI Desktop v March 2023. I connect to a Postgresql v9.6.17 database. 

I have 2 tables, Jobcost with fields like esthours (which is estimated hours), StartDate and Empid. I have another table Employee with a field like Empid, firstname, Lastname. Jobcost.empid is connected to Employee.empid in the relationships pane. Empid in both tables is a string with a length of 6.

Here are the tables and fields again.

Jobcost will have many entries per day for each Empid. 

Jobcost[Empid]

Jobcost[Esthours]

Jobcost[StartDate]

Employee[Empid] (connected to Jobcost[Empid])

Employee[firstname]

Employee[Lastname]

I'm trying to make a matrix to sum hours in a given startDate range by employee. PBI should just sum this by Empid but it doesn't in this case. PBI works in other cases in other PBIX files. 

But pbi gives me all hours summed in the table for every employee so all the values for all employees are the same. My sum is in a measure which looks like: 

Est Hours = SUM(JobCost[estcost])

Example incorrect output: 

John Smith    4001

Sally Green    4001

Jim Jones      4001

The esthours should be different for each employee.

I am unable to upload real data at this time. 

Anyone know how to sum by employee? 

EDIT: I'm making some test data which may help you and me. The test data works like it should but my real data does not. So posting test data will not help at this point. I'm not sure why my real data does not work.

[v-yangliu-msft]

Hi  @croberts21 ,

You can try the following dax:

Measure 2 =
var _id=
MAXX(
    FILTER(ALL('Employee'),'Employee'[firstname]=MAX('Employee'[firstname])),[Empid])
return
SUMX( FILTER(ALLSELECTED(JobCost),'JobCost'[Empid]=_id,'JobCost'[startDate])),[esthours])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[croberts21]

I put the measure on Jobcost and I got this error: 

[sergej_og]

This is often the case when no valid relationship exists between tables (DimTable, FactTable).
Can you post a screen with your datamodel relationships of these two tables?
How is the field JobCost[estcost] defined?