Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TWolsten
Advocate IV
Advocate IV

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")
1 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.

View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.