Forum Discussion

TWolsten's avatar
TWolsten
Advocate IV
4 years ago
Solved

Count unique values within the same time period

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")
Need Help
  • TWolsten's avatar
    TWolsten
    4 years ago

    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.

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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's avatar
      TWolsten
      Advocate IV
      4 years ago

      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.