Forum Discussion

Imrans123's avatar
Imrans123
Advocate V
4 years ago

Counting Rows based on Measure as a Filter

Hey All,    Was wondering if anyone could help me. I have a measure that checks whether a customer has booked a followup session which works perfectly fine. This measure returns a date for each cus...
Show and Tell
Tips and Tricks
Imrans123's avatar
Imrans123
Advocate V
4 years ago

Hi Alexis, 

 

Thanks for giving me some guidance on how I could tackle this problem. I did add some context filter. As per my data model, I can have filters on Practice ID

 

Note: I have the Practice ID column in appointments table and another Practice Table which lists down all the Practices along with their respective names. The slicer filter I have on my page refers to the column Practice Name from the Practices table. Was wondering whether the relationship would allow the context filter to work properly since I am referring to it from the appointments table. 

 

Here is the measure. I believe it's being done correctly, but I don't see much of a performance improvement.

 

Followup Booked =
IF (
NOT ( ISBLANK ( [AppointmentCount] ) ),
VAR MaxDate =
MAX ( 'Appointments'[Date])
RETURN
CALCULATE (
MIN ( 'Appointments'[Date] ),
ALLEXCEPT ( 'Appointments','Appointments'[Practice ID] ),
SUMMARIZE ( 'Appointments', 'Appointments'[Customer ID] ),
'Appointments'[Date] > MaxDate
)
)

 

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

I think a date dimension table would help (along with other dimension tables, e.g., Customer) since it should make it easier to adjust the date filtering independently.

 

I think the measure could become more simple like this:

Followup Booked =
IF (
    NOT ( ISBLANK ( [AppointmentCount] ) ),
    VAR MaxDate = MAX ( 'Appointments'[Date] )
    RETURN
        CALCULATE ( MIN ( dimDate[Date] ), dimDate[Date] > MaxDate, 'Appointments' )
)
  • Imrans123's avatar
    Imrans123
    Advocate V
    4 years ago

    Hey Alexis, 

     

    Thank you so much for giving me a starting point to work with. I've used the above measure, did a bit of trial and error learning about context measures and row iteration and finally got something that looks like this. The speed is at a rate that I would like but some accuracy issues

     

     

     

     

    Followup Booked = 
VAR EndDate = MAX('DimDates'[Date])
RETURN
IF
(
     NOT(ISBLANK([AppointmentCount])),
    CALCULATE (
        MIN('Appointments'[Date]),
        FILTER(
                ALL('DimDates'[Date]),
                'DimDates'[Date] > EndDate
        ),
        ALL('Appointments'),
        SUMMARIZE('Appointments','Appointments'[Customer ID]),
        'Consolidated Appointments'[isBooked]=1
    )
)

     

     

     

    Note, I have another DAX Column which when 1, will be counted in the measure now.

    Also, if I use 

    VAR MaxDate = MAX ( 'Appointments'[Date] )

    The measure slows down quite a bit. However, I have all unique dates in DimDates with a one-to-many relationship. I would assume using Max DimDates would yield the same result?

     

    Using a Year and Month slicer on DimDates, The only two issues I have with this measure now are 

    1. If I use a slicer for Jan 2022, if a customer came in Jan 1st and did a follow up booking on the same month (e.g. Jan 20th) it doesn't count it. 

    2. If I use a slicer for Jan 2022,If a customer came in Jan 1st, then Jan 15th, then booked for Apr 21st. It will show next appointment booked for April 21st wheres it should have been Jan 15th. 

     

    Just looking to work around these issues now. 

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      The difference between MAX ( DimDates[Date] ) and MAX ( Appointments[Date] ) is that the former gives the maximal value within the filter context (Jan 31, 2022, based on a slicer selection of Jan 2022) rather than the maximal date in the appointments table (Jan 15, 2022) for that month.

       

      See if this works any better:

      Followup Booked =
VAR EndDate =
    CALCULATE ( MAX ( 'DimDates'[Date] ), VALUES ( 'Appointments'[Customer ID] ) )
RETURN
    IF (
        NOT ( ISBLANK ( [AppointmentCount] ) ),
        CALCULATE (
            MIN ( 'DimDates'[Date] ),
            FILTER ( ALL ( 'DimDates'[Date] ), 'DimDates'[Date] > EndDate ),
            ALL ( 'Appointments' ),
            SUMMARIZE ( 'Appointments', 'Appointments'[Customer ID] ),
            'Consolidated Appointments'[isBooked] = 1
        )
    )

       

      • Imrans123's avatar
        Imrans123
        Advocate V
        4 years ago

        Hey Alexis, 

         

        Hey Alexis. Again, thanks a tonne for helping me through this.

         

        I've copied your formula as is, however, the figure is much higher than what I expect from it. Furthermore when setting MIN as MIN('DimDates'[Date])..., for each Patient in a matrix, all the dates are the same i.e. If slicer is set for Jan 2022, all dates will be 1st of Feb 2022

         

        . If I revert back the MIN Date to the dates from Appointment Table, I get the same result as I did previously where it's not showing if followup is booked on Jan since Jan doesn't fall within the maximal date as you rightfully pointed out. 

         

        If it helps, once I put VALUES('Appointments'), I get the right result, but pretty counterproductive since it's taking more time to process. I tried using Values from appointment ID which is my primary key in the table. But it still gives me the old result where it doesn't show for current month.