Relationship and cross filter issue

DAX DAX Calculations
1

HI,

Im still having issues with what Im trying to achieve here and i cant quite figure it out.

I want to see what shift an employee is assigned to on each date form my table “Gate Clocking” this has an active relationship that i believe is fine from “Dates table” and the “Shift Codes & Timings table” and also a measure to tell me the expected duration they should be working for example from my attached PBIX employee 100989 on 1/12/2020 was on shift code DW88 and expected duration is 8 hours of work. If i simply drag in the calculated field “Shift Duration” i can see that no problem. However i want it as a measure so i can make the utilization calculation work.

I then want to add the “COOIS” table and the duration that the employee has clocked on the same day as the “Gate clocking” while retaining the complete list of dates in the filtered months.

i have them all working as i want however they filter the dates and only show dates with values when i add the measures. i want to see all the days of the month.

i hope that makes sense to you i have added the PBIX here.

i feel the error is maybe because the shift table has no relationship with the “COOIS” table ??

Capture
Capture1906×956 175 KB
Capture2
Capture21783×673 76.8 KB

Strata Utilization.pbix (2.1 MB)

regards and thanks again

Dan

2

Hi @Krays23

I have made few changes to your Solution and now I can get all Dates irrespective of available Timings.

  1. Used below measure instead of Day Status column.

Day Status_M = max('Gate Clocking'[Day Status])

  1. Changed Expected Shift Duration measures. Don’t understand the requirement with COOIS table in this, if you can clarify will be helpful.

    Expected Shift Duration =
    /CALCULATE(
    SUMX ( ‘Shift Codes & Timings’, ‘Shift Codes & Timings’[Shift Duration] ),
    CROSSFILTER ( COOIS[Finished Date], Dates[Date], Both )
    )    /
    if(isblank([Actual Gate Duration]),blank(),max(‘Shift Codes & Timings’[Shift Duration]))

Please find attached the latest Solution. Changes are made on “Gate Clocking” Page.EDNA Solution_Strata Utilization.pbix (2.1 MB)

Thanks
Ankit J

3

Hi @Krays23, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

1 Like
4

Hi @Krays23, we’ve noticed that no response has been received from you since the 2nd of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Krays23, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!