Forum Discussion
JawnPBI
3 years agoFrequent Visitor
Calculated column not returning what I'm expecting
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 =
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?
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?
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.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
- vicky_
Super User
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
- Ritaf1983
Super User
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.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
- JawnPBIFrequent Visitor
That works!! Thank you 🙂
- Ritaf1983
Super User
Glad to help☺️