Measure to count duplicate appointments (can't add column)

rodwhiteley · August 21, 2023, 9:35am

I’m trying to write a measure to be able to count when our staff have been double-booked.
The only solutions I’ve found online have involved adding a column to the main fact table which I can’t do (the production tables are Direct Query from an SQL DB).

Our primary transactions fact table has a row for every appointment with:
the provider name (“SCH_APPT_RESOURCE”),
a Date/time column for the appointment itself, and a column for each possible appointment state
(“IsCheckin”, “IsCancel”, “IsNoshow”)

(among others columns, not of interest here)

I figured I could created a virtual table with SUMMARIZECOLUMNS

and then use a filter to aggregate the sum of the “Checkins” virtual column, and count the number of times this was >1 (meaning there was more than 1 checkin for that resource, and that date/time), but this fails if I try adding a filter getting an error as I have to pass a scalar:

So if I wrap this in a SUM, the table evaluates:

So I figured I could wrap this expression in a COUNTROWS to find out the number of times there were double-bookings (or worse) but it’s throwing an error I don’t understand:

Note that it will evaluate if I place it on a visual by itself, but it fails if I try to add any other item to aggregate by (real data set has about 1M rows):

To try to figure this out, I created a ToyData set which has 1 double booking (10:00 AM, 5th January, 2023):

(and the PowerBI with the same measure now evaluates rather than throwing an error, but it’s telling me there are 3 “double-booking” rows (I think this should only evaluate to 2):

Weirdly, it will let me aggregate this by the Resource name, but not by the date field:

DuplicateAppts.pbix (40.2 KB)
ToyData.xlsx (8.9 KB)

I’m now lost as I can’t figure out:

Why I am getting an error when I try to place this in a visual with some values, but not others?
Why is the measure evaluating in the Toy data set report, but giving me 3 for the count of duplicates?

N.B. There’s the very real possibility this is completely the wrong approach to this problem, so if anyone has any other ideas, or is able to resolve what I’m doing wrong here, I’d be really grateful

Any help gratefully received,
Rod

HufferD · August 21, 2023, 3:02pm

@rodwhiteley,

I think I understand your needs, but can you clarify why this should be two double bookings rather than three:

Is it because you want to exclude those where isNoShow == 1? If that is the case, then the answer to your question,

is because you aren’t excluding no-shows in your measure.

Maybe the criteria for a double-booking are:

isCheckIn == 1
isNoShow and isCancel are both not equal to 1
The same SCH_APPT_RESOURCE has the same ApptDateTime

if that’s the case, you could approach this by

Filtering the data based on the criteria given,
Grouping the data by ‘SCH_APPT_RESOURCE’ and ‘ApptDateTime’, and
Counting occurrences that are more than one.

a measure like this would handle that:

Double Bookings Count = 
SUMX(
    FILTER(
        SUMMARIZE(
            FILTER(
                'fAppointments', 
                'fAppointments'[IsCheckin] = 1 && 'fAppointments'[IsNoShow] <> 1 && 'fAppointments'[IsCancel] <> 1
            ), 
            'fAppointments'[SCH_APPT_RESOURCE], 
            'fAppointments'[ApptDateTime], 
            "CountOfBookings", COUNTROWS('fAppointments')
        ),
        [CountOfBookings] > 1
    ),
    [CountOfBookings] - 1  // Subtracting to count only the extra bookings
)

Then you could get the count of double-bookings:

rodwhiteley · August 21, 2023, 4:01pm

Thanks for the reply. They should only be double bookings when the state is “IsCheckIn” (there’s another that’s not checked in so it shouldn’t count for this)

HufferD · August 21, 2023, 7:17pm

@rodwhiteley

okay, perhaps the measure i shared will work for you then.

rodwhiteley · August 22, 2023, 9:04am

Thanks indeed. Works perfectly, I really appreciate your help.
Rod