Calculated column not returning what I'm expecting

[JawnPBI]

I am trying to determine from another column if their join time was in the morning or in afternoon. If a join time is AM and leave time is PM, then I want the calculated column to show "AM/PM"
The table context is each row is a record of a person joining a training.
The reason I want to do this is I want to determine how many distinct people were in the morning call and how many people were in the afternoon call.
So if a person joined in the morning and left in the afternoon, it means they were in both AM and PM sessions.

I wrote the following calculated column:

AM/PM Session =

SWITCH(

true(),

AND(CONTAINS('Activity Table','Activity Table'[Join Time], "AM"), CONTAINS('Activity Table','Activity Table'[Leave Time], "PM")), "AM/PM",

AND(CONTAINS('Activity Table','Activity Table'[Join Time], "AM"), CONTAINS('Activity Table','Activity Table'[Leave Time], "AM")), "AM",

AND(CONTAINS('Activity Table', 'Activity Table'[Join Time], "PM"), CONTAINS('Activity Table','Activity Table'[Leave Time], "PM")), "PM",

"N/A"

)

Join and leave time I made both of them text values, and the calculated columns are bringing back "N/A". What am I missing here?

[JawnPBI]

That works!! Thank you 🙂

[Ritaf1983]

Glad to help☺️

[Ritaf1983 · Accepted solution]

Hi @JawnPBI 
Try to modify your formula to :

AM/PM Session Switch =

SWITCH(TRUE(),

    CONTAINSSTRING('Activity table'[Join Time], "AM") && CONTAINSSTRING('Activity table'[Leave Time], "PM"), "AM/PM",

    CONTAINSSTRING('Activity table'[Join Time], "AM"), "AM",

    CONTAINSSTRING('Activity table'[Join Time], "PM"), "PM",

    "N/A"

)

You don't have to have your data in text format it works perfectly with time format too.

Link to the sample file 

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

[vicky_]

I think you just need to swap out the CONTAINS function for the CONTAINSSTRING function. https://learn.microsoft.com/en-us/dax/containsstring-function-dax