Count unique values within the same time period

[TWolsten]

Hi there,

I am trying to get a flag for when two delivery agents are working at the same time in the same location.

Currently my date ranges are working but it is flagging when the same delvierer is deliverying two pieces of work on the same day and I just want it to flag if it is two different deliverers:

I have currently wrote the below but the highlighted bit which is supposed to only count when it is one deliverer doesnt seem to be working?

Any help would be greatly appreicated!

Clash Detection =

VAR StartDate = 'multi-location'[Date/Time From]

VAR EndDate = 'multi-location'[Date/Time To]

VAR Clash =

CALCULATE (

DISTINCTCOUNT( 'multi-location'[Deliverer] ),

ALLEXCEPT ( 'multi-location', 'multi-location'[Level], 'multi-location'[Location] ),

'multi-location'[Date/Time From] <= EndDate,

'multi-location'[Date/Time To] >= StartDate

) > 1

RETURN

IF(Clash, "Clash Detected", "No Clash")

[MFelix]

Hi @TWolsten ,

Without any data is difficult to pin point the error can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[TWolsten · Accepted solution]

Thanks for the reply - turned out to be too much data for the query to handle so I have cut down the dataset and it is now working with the original formula.