Need help Optimizing my DAX Measure

[TonyGu]

Was wondering if anyone could help me with this problem.

I’m building a dashboard for an optometric clinic that has around 10 doctors and many patients. I want to create a measure where I can see if a patient has booked a follow up appointment after visiting a doctor. The next appointment does not necessarily have to be with the same doctor, it could be with any doctor.

The issue I am having is that when I filter by doctor, if a patient books a follow up appointment with another doctor, it does not show up in the visual. For example, if Patient John sees Dr. A and books a follow up appointment with Dr. B, then when I filter Dr. A, it will show that John did not book a follow up appointment.

The measure I have created is as follow,

NextAppointment =

VAR EndDate = LASTDATE(DimDates[Date])

RETURN

IF

(

    NOT(ISBLANK([Appointment Count])),

    CALCULATE (

        FIRSTDATE(Appointment[Appointment Date]),

        FILTER(

                ALL(DimDates[Date]),

                DimDates[Date] > EndDate

        ),

        CROSSFILTER(DimClient[PatientID], Appointment[PatientID],Both)

    )

)

Appointment Count = COUNTROWS(Appointment)

Here, appointment refers to a table which lists all appointments, PatientID refers to the Patient and Dimdates is my date table.

Since this was showing me the wrong figure after filtering out for doctors, I created a DAX Column as follows,

NextAppointmentDate =

VAR _currentDate = Appointment[Appointment Date]

RETURN

CALCULATE (

    MIN ( Appointment[Appointment Date] ),

    ALLEXCEPT ( Appointment, Appointment[PatientID] ),

    Appointment[Appointment Date] > _currentDate

)

This column populates next appointment date for all patients, and as such, if data exists in the column, then I can count it as 1 (meaning the patient has a follow up appointment booked) and thereby addresses the issue I am having.

However, adding this column is making my report very slow. I was wondering whether there is any way to recreate this result using a DAX measure instead of a calculated column or any other efficient way so the report does not slow down??

[OwenAuger]

Hi @TonyGu 

Your logic for the calculated column looks pretty good, and I would recommend more-or-less rewriting that as a measure.

You can leave in the check that Appointment Count is nonblank (as in your original measure) if that is important.

This measure should behave as expected in a visual that groups by DimDate[Date], DimClient[PatientID] and any other columns from Appointment. 

NextAppointment for current patient(s) (measure) =
IF (
    -- Only return result if there are 1 or more appointments
    -- in current context
    NOT ( ISBLANK ( [Appointment Count] ) ),
    VAR MaxDate =
        MAX ( DimDates[Date] )
    RETURN
        CALCULATE (
            MIN ( Appointment[Appointment Date] ),
            ALLEXCEPT ( Appointment, DimClient ),
            DimDates[Date] > MaxDate
            -- ALL ( DimDates ) is not required if DimDates is marked as a Date Table
            -- or if DimDates[Date] is on the 1-side of a relationship
        )
)

 Does this produce the correct result, and perform well enough?

Regards,

Owen

[TonyGu]

Thank you for replying. I have tried your measure and it's not working. It is only returing the earliest next appointment date for one patient. I have attached the image below.

 The left date is correct by using the dax column I created. My issue now is that since I have millions of rows of data. It is super slow when refreshing. So I wonder if it can be solved by using a measure only.

[OwenAuger]

Thanks, that's interesting.

The intention with my measure was certainly to eliminate the calculated column.

In a test model at my end (based on the tables in your description), I do get the same result from my measure and a calculated column using your DAX code (using Earliest aggregation), as long as they are evaluated in the context of particular patient (from Client table) and Date (from DimDates table).

In your table screenshot, which fields are present on the visual apart from Earliest NPD and the measure?

If you are not including columns from Client and DimDates, the measure would need to be rewritten slightly.

[TonyGu]

My rows include doctor name and patient name. So basically I want to show the next appointment date for each patient under each doctor when I expand the rows in the matrix visual.

[OwenAuger · Accepted solution]

Thanks 🙂

You could exactly replicate the behaviour of that calculated column (with "Earliest" aggregation) with a measure like this:

Next Appointment Mimicking Calculated Column with "Earliest" aggregation = 
MINX (
    Appointment,
    VAR CurrentRowDate = Appointment[Appointment Date]
    RETURN
    CALCULATE (
        MIN ( Appointment[Appointment Date] ),
        ALLEXCEPT ( Appointment, Appointment[PatientID] ),
        Appointment[Appointment Date] > CurrentRowDate
    )
)

This measure computes the same expression used for the calculated column for each row of Appointments (in current filter context) within a MINX iterator, so should give the same result as the calculated column regardless of context.

My only thought on this measure is that if you are evaluating it in a context including multiple appointments (e.g. 2+ appointments with the same Doctor/Patient), it may give an unintuitive result. It could be that the earliest "next" appointment is earlier than one of the other appointments in the current context.

An alternative suggestion would be a measure like this (a tweaked version of my earlier measure):

Next Appointment for current patient(s) (measure) = 
IF (
    -- Only return result if there are 1 or more appointments
    -- in current context
    NOT ( ISBLANK ( [Appointment Count] ) ),
    VAR MaxDate =
        MAX ( Appointment[Appointment Date] ) -- Fact table max Appointment Date
    RETURN
        CALCULATE (
            MIN ( Appointment[Appointment Date] ),
            ALL ( Appointment ), -- Clear all filters from Appointment
            SUMMARIZE ( Appointment, DimClient[PatientID] ), -- Retain Patient visible in current context
            Appointment[Appointment Date] > MaxDate -- After Appointment Dates in current context
        )
)

In the case of multiple appointments, this measure will return the next date outside that set for the patient(s) visible in the filter context.

I have attached a PBIX I was using to test in case that's useful.

Regards,

Owen