topic Re: DAX If Statement Based on Time via Direct Query in DAX Commands and Tips

DAX If Statement Based on Time via Direct Query

hankmobley — Sat, 26 Mar 2022 11:06:28 GMT

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.

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Sat, 26 Mar 2022 11:29:57 GMT

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

Re: DAX If Statement Based on Time via Direct Query

hankmobley — Sat, 26 Mar 2022 12:03:28 GMT

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,

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Sat, 26 Mar 2022 12:07:08 GMT

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

Re: DAX If Statement Based on Time via Direct Query

hankmobley — Sat, 26 Mar 2022 12:20:35 GMT

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

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Sat, 26 Mar 2022 12:44:57 GMT

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

Re: DAX If Statement Based on Time via Direct Query

hankmobley — Sat, 26 Mar 2022 12:59:48 GMT

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

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Sat, 26 Mar 2022 13:11:22 GMT

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

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Sat, 26 Mar 2022 13:48:21 GMT

hankmobley

Also make sure this is checked

Re: DAX If Statement Based on Time via Direct Query

hankmobley — Sat, 26 Mar 2022 13:54:23 GMT

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?

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Sat, 26 Mar 2022 14:24:52 GMT

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

Re: DAX If Statement Based on Time via Direct Query

tamerj1 — Wed, 30 Mar 2022 04:20:59 GMT

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

Re: DAX If Statement Based on Time via Direct Query

hankmobley — Wed, 30 Mar 2022 13:26:15 GMT

Thank you so much tamerj1 , that works great!

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