IF Statement measure with Time

[michael_knight]

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

[speedramps · Accepted solution]

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.

[michael_knight]

Perfect, thank you very much

[camargos88]

@michael_knight ,

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

[Greg_Deckler]

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

[michael_knight]

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