Total Count in Measure Not Matching with Table Visual

[markefrody]

Hi,

I have 2 managers wherein their production is measured on the number of sacks that are filled within their work shift excluding their break times.

I am using this measure to determine the sack count for each manager:

Spoiler

sack count =

VAR timeIn = MIN ( 'shift_supervisor_break_times'[Start Time] )

VAR timeOut = MAX ( 'shift_supervisor_break_times'[Clock_Out_Rev] )

VAR timeRange =

FILTER (

VALUES ( 'sack_rail_details'[Date Filled] ),  'sack_rail_details'[Date Filled] >= timeIn

&& 'sack_rail_details'[Date Filled] <= timeOut )

VAR break = DATEDIFF( MIN ( 'shift_supervisor_break_times'[Break1 Start] ), MAX ( 'shift_supervisor_break_times'[Break1 End] ), SECOND)

VAR ss = DATEDIFF ( MINX ( timeRange, 'sack_rail_details'[Date Filled] ), MAXX ( timeRange, 'sack_rail_details'[Date Filled] ), SECOND ) - break

VAR mm = ss / 60

VAR hh = mm / 60

VAR _d1 = MINX ('shift_supervisor_break_times', 'shift_supervisor_break_times'[Start Time] )

VAR _d2 = MINX ('shift_supervisor_break_times', 'shift_supervisor_break_times'[Clock_Out_Rev])

VAR sack_count = CALCULATE(COUNTROWS('sack_rail_details'),FILTER('sack_rail_details','sack_rail_details'[Date Filled] >= _d1 &&

'sack_rail_details'[Date Filled] <= _d2))

RETURN

sack_count

Here is  the manager's schedule for Oct 11, 2021:

And from the production schedule for Oct 11, 2021:

Here is what I got:

If you can see the the total is 386 sacks.

But when using the table form visual, the count is only 221 sacks:

I'm not sure if the measure that I put is correctly capturing the sacks each manager produced within their shift excluding break time since it is not matching with the table visual. 

I have place the pbix file in the below link for reference:
https://www.dropbox.com/s/l961nsj375rkvti/Sacks%20Filled%20Avg%20Rate%20.pbix?dl=0

Appreciate any assistance you can provide. Thanks.

Best regards,
Mark V.

[v-henryk-mstf]

Hi @markefrody ,

I did the test and don't seem to understand the relationship between the tables created in your data model. I think there should be a one-to-many relationship between Date Table and sack_rail_details, which may be the reason for the wrong result of measure filtering.

Please answer my confusion, so that I can do further testing. Looking forward to your feedback.

Best Regards,
Henry

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

[markefrody]

@v-henryk-mstf 

Hi Henry,

Thank you for your response.

I have tried to update "one-to-many" relationship between the Date Table and sack_rail_details table but I am still coming up with the same results. The same is true with the "one-to-many" relationship between the Date Table and shift_supervisor_break_times table. I have made the relationship to the Date Table in order for the other tables have a common field to connect the data. 

I have now updated the relationship to "one-to-many" of Date Table to the other main tables in the link given. 

https://www.dropbox.com/s/l961nsj375rkvti/Sacks%20Filled%20Avg%20Rate%20.pbix?dl=0

Best regards,
Mark V.

[lbendlin]

I don't see where sacks are uniquely linked to supervisors, so you likely experience double counting.  

Can you please explain the rationale behind your data model choices?

Usually you want to create a snowflake schema with dimensions and facts and 1:* relationships.

[markefrody]

Hi @lbendlin,

Thank you for response. There is no unique link between the supervisors to sacks. The link is on the dates wherein:

1. Supervisors
Table: ​shift_supervisor_break_times
Field: Plan Date Only

2. Sacks production
Table: sack_rail_details
Field: Date Filled Date Only

Both of these tables connect to a Date Table which contains "Date" field.