Forum Discussion
IF Statement measure with Time
Hi,
I'm trying to recreate a Conditional Column I've got on another report but as a DAX measure. I'm unable to replicate it in Power Query so I'm trying to create it in measure form
Does anybody know what to do? I'm at a loss
Cheers,
Mike
Hi Micheal
I feel you would be better using a M query
and look up the time of day with a relationship.
But if you must use a DAX measure then try this solution ….Time of day =
// get a time as scalar vaue
Var mytime = MIN(Times[Time])
// get the hour element of the time
Var myhour= HOUR(mytime)
RETURN
SWITCH(
TRUE(),
myhour < 11, "Morning",
myhour < 14, "Afternoon",
myhour < 17, "Late Afternoon",
myhour < 20, "Evening",
BLANK()
)
The big draw back of this soultion is that DAX measures need a scalar, which is why a M query and lookup might be a better solution.
5 Replies
michael_knight Me too, that looks like you are in Power Query but you say you are not??? There are no measures in Power Query, only calculated columns.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Greg_Deckler
Sorry, I don't think I worded it correctly. I have a report which I created the Conditional Column in Power Query, I want to use the same theory with the Times and Late, Morning etc, but in a DAX Measure instead of a Conditional Column. Does that make sense?
Cheers,
Mike
Hi Micheal
I feel you would be better using a M query
and look up the time of day with a relationship.
But if you must use a DAX measure then try this solution ….Time of day =
// get a time as scalar vaue
Var mytime = MIN(Times[Time])
// get the hour element of the time
Var myhour= HOUR(mytime)
RETURN
SWITCH(
TRUE(),
myhour < 11, "Morning",
myhour < 14, "Afternoon",
myhour < 17, "Late Afternoon",
myhour < 20, "Evening",
BLANK()
)
The big draw back of this soultion is that DAX measures need a scalar, which is why a M query and lookup might be a better solution.
Perfect, thank you very much
