USERELATIONSHIP with FILTER

[jaysoulz]

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

[Daniel29195]

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

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

[Anonymous]

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]

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!

[amitchandak]

@jaysoulz , Try like

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

)

[jaysoulz]

It does not seem to work:

Thanks for helping!