Measure to Count with condition, where date slicer considers Earliest Occurrence for each client

[DanielReinman]

I need a measure to report on Client Visit frequencies by provider over selected periods of time - but only if the selected timeframe includes the client's earliest appointment.

So, basically count the number of appointments where the ValidFlag = 1 and where the client's earliest (valid =1) appointment date is included in an AppointmentDate slicer. My model is already large - I need a solution that DOES NOT involve adding additional columns or tables; it must all be contained in measures or virtual tables (otherwise this would be easy).

In the example data below, if my Date slicer was filtered to 01/15 - 01/30, the measure would only count the 3 highlighted Appointments, because Jon's, Jay's, and Eric's earliest Valid appointments fall outside of the Date slicer range. (Date Table is joined to AppointmentDate).

[Anonymous]

Thank you rajendraongole1  and FarhanJeelani 

Hi, @DanielReinman 

Based on your description, I have the following sample data:

I created a slicer using the date column of the date table. Then here are my measurements:

Measure 2 = 
VAR _min_slicerdate = MINX('Date','Date'[Date])
VAR _max_slicerdate = MAXX('Date','Date'[Date])
VAR _user1= SELECTCOLUMNS(FILTER(ALL('Appointment'),'Appointment'[ValidFlag] = 1 && 'Appointment'[AppointmentDate] <=_min_slicerdate ),'Appointment'[Client],'Appointment'[ValidFlag])
VAR _user2 = SELECTCOLUMNS(FILTER(ALL('Appointment'),'Appointment'[AppointmentDate]<=_max_slicerdate&&'Appointment'[AppointmentDate]>=_min_slicerdate && 'Appointment'[ValidFlag] = 1),'Appointment'[Client],'Appointment'[ValidFlag])
VAR _in_slicerdate_user = EXCEPT(_user2,_user1)
RETURN COUNTAX(_in_slicerdate_user,'Appointment'[Client])

Let me briefly explain this solution:

user1 will return all records that are less than &&ValidFlag=1 prior to the date slicer minimum. The same user2 will return all records between &&&ValidFlag = 1 on the date selected by the date slicer.

Then, using excep, subtract user1, a user who is not outside the date range, from the user between the selected dates.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DanielReinman]

Thank you - this works well to get the total, if you assume that the example fields exist in isolation, but the data exists in a relational model, and it seems the "All" criteria is preventing additional analysis and filtering on the measure.

[Anonymous · Accepted solution]

Hi, @DanielReinman 

Trying the expression below, I modified the _user2. This will allow slicers of other dimensions to filter it:

Measure 2 = 
VAR _min_slicerdate = MINX('Date','Date'[Date])
VAR _max_slicerdate = MAXX('Date','Date'[Date])
VAR _user1= SELECTCOLUMNS(FILTER(ALL('Appointment'),'Appointment'[ValidFlag] = 1 && 'Appointment'[AppointmentDate] <=_min_slicerdate ),'Appointment'[Client],'Appointment'[ValidFlag])
VAR _user2 = SELECTCOLUMNS(FILTER('Appointment','Appointment'[AppointmentDate]<=_max_slicerdate&&'Appointment'[AppointmentDate]>=_min_slicerdate && 'Appointment'[ValidFlag] = 1),'Appointment'[Client],'Appointment'[ValidFlag])
VAR _in_slicerdate_user = EXCEPT(_user2,_user1)
RETURN COUNTAX(_in_slicerdate_user,'Appointment'[Client])

Here are the results:

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DanielReinman]

That works!  Thank you so much for your assistance!

[FarhanJeelani]

Hi @DanielReinman ,

Please try the below:

Measure: Count Valid Appointments

CountValidAppointments = 
VAR SelectedDateRange = 
    VALUES('Date Table'[Date])  -- Gets the selected date range from the slicer

VAR EarliestValidAppointments = 
    SUMMARIZE(
        FILTER(
            'Appointment Table',
            'Appointment Table'[ValidFlag] = 1
        ),
        'Appointment Table'[Client],
        "EarliestDate", MIN('Appointment Table'[AppointmentDate])
    )

VAR ValidAppointmentsInRange =
    FILTER(
        'Appointment Table',
        'Appointment Table'[ValidFlag] = 1 &&
        'Appointment Table'[AppointmentDate] IN SelectedDateRange &&
        'Appointment Table'[AppointmentDate] = 
            CALCULATE(
                MINX(
                    FILTER(
                        EarliestValidAppointments,
                        'Appointment Table'[Client] = [Client]
                    ),
                    [EarliestDate]
                )
            )
    )

RETURN
    COUNTROWS(ValidAppointmentsInRange)

Explanation:

1. SelectedDateRange: Captures the dates selected by the slicer.
2. EarliestValidAppointments: Calculates the earliest valid appointment date for each client (only those with ValidFlag = 1).
3. ValidAppointmentsInRange: Filters the Appointment Table for:
   • Appointments with ValidFlag = 1.
   • Appointments within the selected date range.
   • Appointments that match the earliest valid appointment date for the respective client.
4. COUNTROWS: Counts the number of valid appointments meeting the criteria.

Key Notes:

• This measure avoids creating additional columns or tables by using virtual tables and dynamic filtering.
• Ensure your model's relationships are correctly set up between the Date Table and Appointment Table via AppointmentDate.

Please mark this as solution if it helps. Appreciate Kudos

[DanielReinman]

Thanks so much for the suggestion - looks like your solution does well to identify the count of clients with new appointments, but it does not appear to count all of the clients' appointments that were not the earliest ones, though they fall within the specificed time period.

[rajendraongole1]

Hi @DanielReinman - you can create a measure in Power BI that calculates the count of valid appointments where the client's earliest valid appointment falls within the selected slicer range.

ValidAppointmentsCount :=
VAR SelectedDateRange =
FILTER(
ALL('Date'),
'Date'[Date] >= MIN('Date'[Date]) &&
'Date'[Date] <= MAX('Date'[Date])
)
VAR EarliestAppointments =
SUMMARIZE(
FILTER(
'AppointmentTable',
'AppointmentTable'[ValidFlag] = 1
),
'AppointmentTable'[Client],
"EarliestDate", MIN('AppointmentTable'[AppointmentDate])
)
VAR FilteredClients =
FILTER(
EarliestAppointments,
[EarliestDate] IN SelectedDateRange
)
VAR Result =
COUNTROWS(
FILTER(
'AppointmentTable',
'AppointmentTable'[ValidFlag] = 1 &&
'AppointmentTable'[Client] IN SELECTCOLUMNS(FilteredClients, "Client", [Client])
)
)
RETURN
Result

Hope this helps.