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 
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?

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      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