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

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

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

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

      hankmobley 

      Also make sure this is checked

       

      • hankmobley's avatar
        hankmobley
        Helper I
        4 years ago

        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?