I am trying to calculate the shift type based on the sign in, but the folumar is not working

[KatrinaG]

Hi All, 

What is wrong with this formular? 

I am trying to assign the shift type, but it gives me the error: 

Shift =

IF(

WEEKDAY( VALUES(P259_tblJobs[CrewDttm].[Date]), 2) > 5,

"Weekend",

IF(

AND(

WEEKDAY( VALUES(P259_tblJobs[CrewDttm].[Date]), 2) <= 5,

AND(

VALUES(P259_tblJobs[Start Time]) >= 6/24,

VALUES(P259_tblJobs[Start Time]) <= 14/24

)

),

"Day",

IF(

AND(

WEEKDAY( VALUES(P259_tblJobs[CrewDttm].[Date]), 2) <= 5,

AND(

VALUES(P259_tblJobs[Start Time]) >= 14/24,

VALUES(P259_tblJobs[Start Time]) <= 22/24

)

),

"Afternoon",

"Night"

)

)

)

Many thanks in advance 

[gmsamborn]

Hi @KatrinaG 

In your measure, try changing VALUES( to SELECTEDVALUE( and it should work for you.

[gmsamborn · Accepted solution]

@KatrinaG 

Maybe try this.

Shift 2 = 
VAR _Date = SELECTEDVALUE( P259_tblJobs[CrewDttm] )
VAR _WkDay = WEEKDAY( _Date, 2 )
VAR _StartTime = SELECTEDVALUE( P259_tblJobs[Start Time] )
VAR _Result =
    IF(
        _WkDay > 5,
        "Weekend",
        IF(
            _WkDay <= 5
                && _StartTime >= 6 / 24
                && _StartTime <= 14 / 24,
			"Day",
            IF(
                _WkDay <= 5
					&& _StartTime >= 14 / 24
                    && _StartTime <= 22 / 24,
            "Afternoon",
            "Night"
            )
        )
    )
RETURN
    _Result

Calculate shift type.pbix

Let me know if you have any questions.

[KatrinaG]

thanks for the solution, much appreciated! 
However, this has aslo created multiple duplicated lines  across the entire table 😞 

[gmsamborn]

Hi @KatrinaG 

Can you replicate the problem using test data?

I really can't think of any reason for the duplicates.  (It's the same as your DAX but with a few minor changes and it seems to work in my example.)

[KatrinaG]

 Thanks for the advice, I've actualy found the saurce of the problem. It is driven by notes from tblJobs-Custom, but I still need this column as there is one conditional columns linked to it. 
How can I forward you the sample? 

Thanks again!  

[gmsamborn]

Hi @KatrinaG 

Can you check your IMs?

Thanks

[KatrinaG]

Attaching the snap shot of the refence data in the table: