cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Post Prodigy

## 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

1 ACCEPTED SOLUTION
Super User

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 5
Super User

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.

Post Prodigy

Perfect, thank you very much

Community Champion

If you have null values you are gonna get errors. Start testing the null values.

Proud to be a Super User!

Super User

@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.

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

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