DAX If Statement Based on Time via Direct Query

[hankmobley]

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:

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:

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

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?

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:

Happy to provide more information if needed.

Thanks.

[tamerj1 · Accepted solution]

@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] )
    )

[hankmobley]

Thank you so much @tamerj1 , that works great! 

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

[tamerj1]

@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"
    )

[hankmobley]

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):

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

[tamerj1]

@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"
        )
)

[tamerj1]

@hankmobley 

Also make sure this is checked

 

[hankmobley]

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:

Is there another workaround using DAX?

[tamerj1]

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

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] )
	)

[hankmobley]

Hi @tamerj1 ,

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

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

[tamerj1]

@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

[hankmobley]

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):