Forum Discussion

jaysoulz's avatar
jaysoulz
Helper I
2 years ago

USERELATIONSHIP with FILTER

Hi,

I am trying to display the correct timing from my worker and I am having the issue where I need two dates (one for Start Time ; Stop Time).

Calendar [Date] is linked to DATA [StartDate] (active) and DATA [StopDate] (inactive)



Start time is showing correctly as it is the same date as the Slicer:

 
However, I am seeing a blank when I tried to input the USERELATIONSHIP for the Stop Time:

StopDateTime =
CALCULATE(
    MAX(DATA[StopDateTime]),
    FILTER(
        DATA,
        HOUR(DATA[StopDateTime]) < 12
    ),
    USERELATIONSHIP(DATA[StopDate], 'Calendar'[Date])
)

Result:

 

In order to make it works, I need Start Time as Nov 13 (as slicer - MIN from PM) and Stop Time Nov 14 (MAX from AM).

 
Expected result:

Anyone know how to?

 
Thanks
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    jaysoulz , Try like

     

    StopDateTime =
    CALCULATE(CALCULATE(
    MAX(DATA[StopDateTime]),USERELATIONSHIP(DATA[StopDate], 'Calendar'[Date]))
    FILTER(
    DATA,
    HOUR(DATA[StopDateTime]) < 12
    )

    )

    • jaysoulz's avatar
      jaysoulz
      Helper I
      2 years ago

      It does not seem to work:




      Thanks for helping!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jaysoulz ,

    The DAX formula you provided seems almost correct, but I would suggest a slight modification:

    StopDateTime =
CALCULATE(
    MAX(DATA[StopDateTime]),
    USERELATIONSHIP(DATA[StopDate], 'Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] <= MAX('Calendar'[Date])
    ),
    HOUR(DATA[StopDateTime]) < 12
)

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

    • jaysoulz's avatar
      jaysoulz
      Helper I
      2 years ago

      I change your DAX into

       

      StopDateTimeRon = 
CALCULATE(
    MAX(REPORT[StopDateTime]),
    USERELATIONSHIP(REPORT[StopDate], 'Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] <= MAX(REPORT[StopDateTime])
    ),
    HOUR(REPORT[StopDateTime]) < 12
)

       

      However, it does not provide all the correct time:


      Thanks for the help!

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    jaysoulz 

     

    StopDateTime =
CALCULATE(
    MAX(DATA[StopDateTime]),
    FILTER(
        all(DATA),
        HOUR(DATA[StopDateTime]) < 12
    ),
    USERELATIONSHIP(DATA[StopDate], 'Calendar'[Date])
)

     

     

    let me know if this works for you . 

    • jaysoulz's avatar
      jaysoulz
      Helper I
      2 years ago

      Nope. All results are 2023-12-31 11:56:51AM. The result date doesnt match the Punch Out date...