Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
michael_knight
Post Prodigy
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

 

help.PNG

 

Does anybody know what to do? I'm at a loss

 

Cheers,

Mike

1 ACCEPTED SOLUTION
speedramps
Super User
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.

View solution in original post

5 REPLIES 5
speedramps
Super User
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.

Perfect, thank you very much

 

wrgrf.PNG

camargos88
Community Champion
Community Champion

@michael_knight ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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

 

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.