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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
hankmobley
Helper I
Helper I

DAX If Statement Based on Time via Direct Query

Hi,

Seeing as this is a DAX issue, I thought I would try my luck here with the DAX experts. 

I currently have an 'execution_time' column in the format below:

hankmobley_0-1648291142742.png

What I would like is to create a new column which simply states "Day" if the time is between 7am-8:59pm, and "Night" if it's between 9pm-6:59am.

I found this formula which I thought could work for me, but when I tested it, it only returns "Night" under all conditions:

hankmobley_1-1648291600725.png

And when I amended the formula to suit my needs, the new column doesn't bring up anything (blank):

hankmobley_2-1648291791016.png

Also, just to provide some background, I'm running data via Direct Query, so it doesn't allow me to change the 'execution_time' format by creating a new column in the desktop.  It looks like the FORMAT function is not supported by Direct Query?

hankmobley_4-1648292047261.png

I also tried changing the format by going to the Model view, clicking on the 'execution_time' column name and then under Properties, changing the format to Time only, but as you can see, this had no impact on the result:

hankmobley_5-1648292315810.png

hankmobley_6-1648292368516.png

Happy to provide more information if needed.

Thanks.

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

@hankmobley 
Here is the complete solution
When using direct query, creating a calculated column based on time calculations is not allowed. Therefore, we can follow these steps to work around it
1. Create Day/Night measure based on time calculations

 

DayNightt Measure =
MAXX (
   autofxx_order_leg,
    VARCurrentTimee =
        TIME ( HOUR (autofxx_order_leg[execution_time] ), MINUTE (autofxx_order_leg[execution_time] ), SECOND (autofxx_order_leg[execution_time] ) )
    RETURN
        IF (
           CurrentTimee > TIMEVALUE ( "07:00 AM" )
                &&CurrentTimee <= TIMEVALUE ( "09:00 PM" ),
            "Day",
            "Night"
        )
)

 

2. Create a disconnected slicer table:

 

Slicer Day/Night = SELECTCOLUMNS ( { "Day", "Night" }, "Day/Night", [Value] )

 

3. Recreate the existing measures following this template

 

New Measure =
VARSelectedDayNightt =
    SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
    CALCULATE (
        [Old Measure],
        FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
    )

 

4. Create the slicer from the Day/Night slicer table and use the new measure in your visual
5. If columns are required in the visual they shall be added as measures, example:

 

Profit =
VARSelectedDayNightt =
    SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
    IF (
        HASONEVALUE ( 'Slicer Day/Night'[Day/Night] ),
        CALCULATE (
            SUM (autofxx_order_leg[portfolio_base_pnl] ),
            FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
        ),
        SUM (autofxx_order_leg[portfolio_base_pnl] )
    )

 

 

View solution in original post

12 REPLIES 12
tamerj1
Super User
Super User

@hankmobley 
Here is the complete solution
When using direct query, creating a calculated column based on time calculations is not allowed. Therefore, we can follow these steps to work around it
1. Create Day/Night measure based on time calculations

 

DayNightt Measure =
MAXX (
   autofxx_order_leg,
    VARCurrentTimee =
        TIME ( HOUR (autofxx_order_leg[execution_time] ), MINUTE (autofxx_order_leg[execution_time] ), SECOND (autofxx_order_leg[execution_time] ) )
    RETURN
        IF (
           CurrentTimee > TIMEVALUE ( "07:00 AM" )
                &&CurrentTimee <= TIMEVALUE ( "09:00 PM" ),
            "Day",
            "Night"
        )
)

 

2. Create a disconnected slicer table:

 

Slicer Day/Night = SELECTCOLUMNS ( { "Day", "Night" }, "Day/Night", [Value] )

 

3. Recreate the existing measures following this template

 

New Measure =
VARSelectedDayNightt =
    SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
    CALCULATE (
        [Old Measure],
        FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
    )

 

4. Create the slicer from the Day/Night slicer table and use the new measure in your visual
5. If columns are required in the visual they shall be added as measures, example:

 

Profit =
VARSelectedDayNightt =
    SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
    IF (
        HASONEVALUE ( 'Slicer Day/Night'[Day/Night] ),
        CALCULATE (
            SUM (autofxx_order_leg[portfolio_base_pnl] ),
            FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
        ),
        SUM (autofxx_order_leg[portfolio_base_pnl] )
    )

 

 

Thank you so much @tamerj1 , that works great! 

I really appreciate the time and effort you have taken to helping me 😊

tamerj1
Super User
Super User

@hankmobley 
Try this for a calculated column

DayNight =
VAR CurrentTime =
    TIME ( HOUR ( autofx_order_leg[execution_time] ), MINUTE ( autofx_order_leg[execution_time] ), SECOND ( autofx_order_leg[execution_time] ) )
RETURN
    IF (
        CurrentTime > TIMEVALUE ( "07:00 AM" )
            && CurrentTime <= TIMEVALUE ( "09:00 PM" ),
        "Day",
        "Night"
    )

Hi @tamerj1 ,

Thanks, but I"m still getting the same error message when I add your formula in a new column, and then use it in a visual (screenshot below):

hankmobley_0-1648299122046.png

Could it be the way the 'execution_time' column is formatted from DQ (screenshot below):

hankmobley_1-1648299221606.png

 

@hankmobley 
Ok then try adding it as a measure. Use the following code

DayNight Measure =
MAXX (
    autofx_order_leg,
    VAR CurrentTime =
        TIME ( HOUR ( autofx_order_leg[execution_time] ), MINUTE ( autofx_order_leg[execution_time] ), SECOND ( autofx_order_leg[execution_time] ) )
    RETURN
        IF (
            CurrentTime > TIMEVALUE ( "07:00 AM" )
                && CurrentTime <= TIMEVALUE ( "09:00 PM" ),
            "Day",
            "Night"
        )
)

@hankmobley 

Also make sure this is checked

6C9C5A01-504E-4B6D-B376-483E986C2367.png

Thanks @tamerj1 

The measure does work on its own, but now I am not able to us the day/night data as part of my visuals that use slicers/filters.  If I'm not mistaken, only columns work with slicers/filters?

I also checked the DQ options, and I don't have the same option you are showing.  This is the only option I see under DQ:

hankmobley_0-1648302669755.png

Is there another workaround using DAX?

 

@hankmobley 

Apparently dq does not allow creating collumns baes on time. This resonable.  Best t I think we can work around that.  
Am not on my compter right now. Will get back to you on Monday morning. Apologies for that 

tamerj1
Super User
Super User

Hi @hankmobley 
You can store the time of the current row in a variable then use it in IF or SWITCH

AVR CurrentTime = 
	TIME ( 
		HOUR ( autofx_order_leg[execution_time] ), 
		MINUTE ( autofx_order_leg[execution_time] ), 
		SECOND ( autofx_order_leg[execution_time] )
	)

Hi @tamerj1 ,

Thanks for your reply.  I created a new column and added your formula, but I get the below error:

hankmobley_0-1648296082597.png

Also, I'm not sure what you mean by "...then use it in IF or SWITCH".  Sorry, PBI is relatively new to me and I'm still learning.

Thanks,

H

 

@hankmobley 

No worries can you please share the formula you've used. 
My understanding that you wanted to create a calculated column not a measure. But again no worries it can be converted into a measure. Just paste your code in a reply and I will edit it for you

Hi @tamerj1 ,

I did provide screenshots above of the code I'm using, but I probably should have provided them in code form instead, sorry.  Also, I am trying to create a column with Day/Night, not a measure.  t's my understanding (although I could be wrong) that you can only use slicers on columns and not measures?

Here is the code I'm using to calculate Day/Night:

DayNight = 
IF (
    autofx_order_leg[execution_time] > TIMEVALUE("07:00 AM")
        && autofx_order_leg[execution_time] <= TIMEVALUE("09:00 PM"),
        "Day",
    IF (
        autofx_order_leg[execution_time] > TIMEVALUE("09:00 PM")
            && autofx_order_leg[execution_time] <= TIMEVALUE("07:00 AM"),
            "Night"
    )
)

As mentioned, this code doesn't bring anything up (blank).  To check this, I just opened up a new table that shows the 'execution_time' column next to the new 'Day/Night' column (screenshot below):

hankmobley_0-1648297131942.png

 

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.