Forum Discussion
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 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?
15 Replies
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 )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?
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).
Can you share the DAX for your [Followup Booked] measure?
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)
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] ) ) )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)
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.
