Forum Discussion

hankmobley's avatar
hankmobley
Helper I
4 years ago
Solved

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...
Need Help DAX
  • tamerj1's avatar
    tamerj1
    4 years ago

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

     

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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's avatar
hankmobley
Helper I
4 years ago

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's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      hankmobley
      Helper I
      4 years ago

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