Forum Discussion
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:
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.
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 calculationsDayNightt 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] ) )
12 Replies
Hi hankmobley
You can store the time of the current row in a variable then use it in IF or SWITCHAVR 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:
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
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
Try this for a calculated columnDayNight = 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):
Could it be the way the 'execution_time' column is formatted from DQ (screenshot below):
hankmobley
Ok then try adding it as a measure. Use the following codeDayNight 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
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 calculationsDayNightt 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 😊
