Counting Rows based on Measure as a Filter

[Imrans123]

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 customer. 

Now, I want to count all the rows where a customer has a followup session booked. I had this measure built, but with scaling, I've been having memory max out when I put this particular measure onto my visual. 

Followup Count = 

COUNTROWS
(
FILTER
(
ADDCOLUMNS
(
VALUES('Customer'[Customer ID]),
"Followup",[Followup Booked]
),
NOT ISBLANK([Followup])
)
)

I have created alternate measures like using SUMX(Values and nested if statement, but it does not render the visual as fast as I would hope. Anyone know the best practice for counting rows using a measure filter like above?

[jdbuchanan71]

@Imrans123 

I am wondering if this gives you the same result with better performance?

Followup Booked =
VAR ACount = [AppointmentCount]
VAR MaxDate = MAX ( 'Appointments'[Date] )
RETURN
    CALCULATE (
        MIN ( 'Appointments'[Date] ),
        ALLEXCEPT ( 'Appointments', 'Appointments'[Customer ID] ),
        'Appointments'[Date] > MaxDate
    ) * DIVIDE ( ACount, ACount )

[Imrans123]

Still taking too long. What's interesting about this situation though is my report is slow for large datasets even if I use a slicer filter. For instance, in a report with less rows of data, the visual loads pretty quickly. 

But if I were to attempt to render the same visual in a larger file but using a slicer filter to filter out rows and get it at the same number as the first scenario, it still seems to struggle and max out on memory. Is there something I could config here? Or would I have to reduce data from Power Query?

[AlexisOlson]

@jdbuchanan71's DAX is what I would recommend too without knowing anything else about your model.

I think what you're observing is due to the fact that you're using ALL (or ALLEXCEPT). The only filter context you're preserving is [Customer ID], so slicers on anything else don't help reduce the size that you're calculating the minimum over.

Is there any other filter context that you can preserve and include in ALLEXCEPT? Alternatively, you could use ALL only on columns that you specifically want to remove the filter context for (specifying what context to remove rather than what to keep).

[Imrans123]

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]

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]

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]

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]

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. 

[Imrans123]

Ahh, I understand what you mean. But filtering out Customer ID should be the most effective way to go on about it.

The only other context filter can be a "Practice ID" which specifies which practice a Customer is registered with. i.e. if a Customer can only book a followup appointment with the practice where he/she is registered.  

Alternatively for All, it would be the Date and Counsellor ID. I.e. if I put a filter on a date slicer for Feb 2022, a Followup may have been booked for March or later which needs to be included in the measure.

[AlexisOlson]

This might be a bit more efficient but the [Followup Booked] measure should be optimized first:

Followup Count =
COUNTROWS (
    FILTER (
        VALUES ( 'Customer'[Customer ID] ),
        NOT ISBLANK ( [Followup Booked] )
    )
)

[Imrans123]

Thanks. That had made it faster than before! 

Here is the measure:

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

The nested measure, AppointmentCount is as follows, 

AppointmentCount = COUNTROWS(Appointments)

[v-chenwuz-msft]

Hi @Imrans123 ,

Please replace  SUMMARIZE() by SUMMARIZECOLUMNS() function. 

The SUMMARIZE() function is traditionally used to group columns and return resulting aggregations. However, the SUMMARIZECOLUMNS() function is newer and more optimized. Use that instead.

If possible, create a table instead of use addcolumns().

Measures are calculated iteratively by default. If measure definitions use iterative functions such as AddColumns(), Power BI creates nested iterations, which negatively affect report performance.

Best Regards

Community Support Team _ chenwu zhu

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

[AlexisOlson]

SUMMARIZECOLUMNS is not appropriate here since it cannot be used within a context transition.

https://dax.guide/summarizecolumns/

SUMMARIZECOLUMNS does not support evaluation within a context transition. This makes it not useful in most of the measures – a measure with SUMMARIZECOLUMNS can be called also by CALCULATE but not in any case of context transition, including other SUMMARIZECOLUMNS statements. Client tools like Excel and Power BI almost always generate context transitions to evaluate measures in the reports.

[jdbuchanan71]

Can you share the DAX for your [Followup Booked] measure?

[Imrans123]

Here is the measure:

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

The nested measure, AppointmentCount is as follows, 

AppointmentCount = COUNTROWS(Appointments)