Performance issue when this Measure is applied to Filter (on Matrix visual)

[JustinDoh1]

This thread is related to my previous thread which I am still trying to figure out.

I marked the previous thread as "solved" because I got enough recommendations to approach the issue (but I still could not make it work).

I have uploaded a large PBIX file here (because I have to show an example with a large data) since this is related to performance.

What you will see is a matrix with four layers of hierachy.

When this particular measure (bottom) is applied to the filter, it takes forever to load data.

decimalFixNewAdd = IF([decimalFixNew] <> BLANK(), 1, 0)

When there is not much data, it works ok (not great), but with so much data, I am thinking there should be a better way..

I spent at least last 2-3 weeks to tweak anything (tried to use different cardinality or delete other visuals etc.) to make it work, but I am not sure how.

This file might run and get the result after few minutes, but when I publish to PowerBI Service, it crashes with an error saying "it ran out of memory".

Is there a way to aggregate data?

Any suggestions?  Thanks!

[JustinDoh1]

@MarkLaf  Thank you so much for your help.

I am not sure how/where to implement the second measure you indicated:

IF( HASONEVALUE( DimDate1[Date] ), "0", "0.0" )

Is there any setting that I have to modify on the PowerBI desktop by itself?

I was looking at this article (link on the bottom), but on my desktop version, I do not see it.

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/...

I do not see under "Preview features"-->

Thanks.

[MarkLaf · Accepted solution]

Yes, it's a little confusing. According to the main MS doc (https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings) it's still in preview BUT I believe they have simultanesouly made it generally available - i.e. it should be enabled by default. At least in the PBI desktop version I have, 2.139.2054.0 64-bit (January 2025), it is just on and there is no toggle in the Preview features settings. That said, you may need to enable it in Preview features setting if you have an older version of desktop.

With that aside, there are a couple ways to get to get to dynamic format string formula. Probably the easiest is to just select your measure in Report or Table view and then select 'Dynamic' in the format dropdown:

Once that is on, with your measure selected, you get a dropdown to the left of your formula bar that lets you navigate to the DAX for Measure vs. Format:

Note: I just added the above info into my main response so all relevant info is in one post.

[JustinDoh1]

@MarkLaf 

Thank you so much for your help. It worked beautifully.

Your screenshots really helped me to understand.

Before I accept as solution, I possibly have one question. I am trying to understand why your measure worked and concept.

You mentioned about "When a measure throughout a matrix row evaluates to blank, the whole row is hidden. You don't need to do any filtering on top of that. You may be seeing weird behavior because I don't believe a matrix displays more than 100 columns. I.e., a row looks like all blank / should be hidden, but there are values in columns off the screen keeping it in view."

Can you please explain to me what you meant? Thank you so much!

[MarkLaf]

When I opened up the pbix you had shared, there was no filter on the date columns, so ALL the dates for your whole calendar were in the matrix columns but also you could only see the first 100 (hard matrix limitation). Because of this, some blank-looking (ie no data in first 100 days) rows were still in the visual (because they had data in the 100+ days that were not visible).

I was just guessing that perhaps you were getting confused by that when you tried to implement the measure filter, which seemed to be trying to filter out rows that were blank. You can just ignore the comment if that particular view (no filters on dates) wasn't a factor in your thinking.

[JustinDoh1]

@MarkLaf 

Thank you for your feedback.

Sorry, I am still not understanding what you are saying...(I read over and over but..).

I have two questions:

1) Regards to how your method totally make the visual performant, is it because measure was not applied to Filter?

2) I am little bit stuck on you used "Countrows" (instead of using 

count('vw_census_trend1'[ClientID]). Is there any logic behind it?

AVERAGEX (

        VALUES ( DimDate1[Date] ),

        CALCULATE (

                    COUNTROWS ( vw_census_trend1 )

                            )

                    )

Thanks!

[JustinDoh1]

@MarkLaf I would like to follow up on my two questions.

[MarkLaf]

There were a few things throughout your measures that were slowing down your report - I didn't check to see what had the most impact, so with no particular emphasis:

ClientD_M1 = count('vw_census_trend1'[ClientID]) + 0

1) Usually it is better to avoid adding zero like this as it means the measure will never be blank - i.e. will get evaluated/displayed everywhere. This can impact performance. I did a quick search and this post has a good explanation of this: https://community.fabric.microsoft.com/t5/Desktop/Adding-0-on-Measure-Directly-Impacts-Performance-M...
Note: if you are only using this over an iterator, then the performance issue is mitigated as you are only evaluating for each row of the iterator within the filter context (so you will get blanks when the table you are iterating over is empty in filter context). The solution I shared took this further by also checking up front to filter out cases where we know that no census data exist.

2) It makes sense to use COUNTROWS instead of COUNT in a case like this because a) it reflects the intent better, I think (are we interested in the count of all non-blank IDs or the count of applicable census rows), and b) COUNTROWS is a little more efficient than COUNT so as a general rule it is better to use unless your specific scenario calls for COUNT. The performance difference is not big, though (difference of milliseconds in most scenarios), so you could argue it's just preference. This is actually covered in official docs: https://learn.microsoft.com/en-us/dax/best-practices/dax-countrows

AverageNew = 
AVERAGEX(
    FILTER(
        SUMMARIZE(
            'vw_census_trend1',
            DimDate1[DateFormat],
            "ClientD_M1", [ClientD_M1]
        ),
        [ClientD_M1] > 0
    ),
            [ClientD_M1]
)

3) This measure is sort of doing the same thing three times in the first argument of AVERAGEX. SUMMARIZE( 'vw_census_trend1', DimDate1[DateFormat] ) by itself gives you all the DimDate1[DateFormat] for which related 'vw_census_trend1' rows exist. Thus, adding a column where you are counting related 'vw_census_trend1' rows and then performing a >0 filter on that is adding a couple operations to achieve something that's already been accomplished. Check out this on more info on SUMMARIZE + note the warning on using it to add columns: https://dax.guide/summarize/

4) The outermost AVERAGEX component is what you are really after. It's very close to the solution that I shared. You do, though, have the issue where you are only iterating over "DimDate1[DateFormat] for which related 'vw_census_trend1' rows exist" - e.g. if filter context of visual in a Date Total column is { Jan 1, Jan 2, Jan 3 } but you only have census rows related to Jan 2 and Jan 3, then Jan 1 won't be included in the table you are iterating over. I.e. this is why your measure wasn't factoring in days where count was 0. Or maybe that's what you wanted, in which case this is not an issue at all.

decimalFixNew = 
if(DISTINCTCOUNT(DimDate1[DateFormat])=1,
format([AverageNew],"0"),
format([AverageNew],"0.0")
)

5) What you are looking for here is HASONEVALUE, which you probably saw we used in the dynamic format string. You can probably immediately see how this would perform a lot better: checking if given column just has a single value in filter context VS performing a distinct count on the whole column within filter context and then checking if it's =1. 

6) Another issue here is that FORMAT( BLANK(), "0" ) will evaluate to "" not BLANK(). This is why you were seeing "blank" rows with this measure. Besides not being what you wanted, this would also have a performance impact.

7) To be clear, converting this measure to a dynamic format string I don't think should have improved the performance at all. The gains related to this measure came from the two points above

decimalFixNewAdd = IF([decimalFixNew] <> BLANK(), 1, 0)

8​) As noted above, this shouldn't be needed if you fix the FORMAT issue noted in #6. And for what it's worth, you could have forgone making another measure and just did a 'is not empty' filter on [decimalFixNew]. I don't think that would have made a difference performance-wise, though

[MarkLaf]

When a measure throughout a matrix row evaluates to blank, the whole row is hidden. You don't need to do any filtering on top of that. You may be seeing weird behavior because I don't believe a matrix displays more than 100 columns. I.e., a row looks like all blank / should be hidden, but there are values in columns off the screen keeping it in view.

That said, as far as I can tell, you can replace your handful of measures with the below to get the same, more performant result:

Daily Avg = 
AVERAGEX( VALUES( DimDate1[DateKey] ), CALCULATE( COUNTROWS( vw_census_trend1 ) ) )

Turn on dynamic format for the measure and use this for the 'int on single date, decminal on aggregation' format:

IF( HASONEVALUE( DimDate1[Date] ), "0", "0.0" )

If not already familiar, there are a couple ways to get to get to dynamic format string formula. Probably the easiest is to just select your measure in Report or Table view and then select 'Dynamic' in the format dropdown:

Once that is on, with your measure selected, you get a dropdown to the left of your formula bar that lets you navigate to the DAX for Measure vs. Format:

Also, one note that the measure currently only averages the count when a census row exists for that day in the filter context - i.e. blanks are not counted as 0 within the average. E.g. the below total average is 1.0, not 0.75:

If you want to count blank dates in the average - and on same token, hide rows where there are no customers (i.e. hide 0's), something like this would work:

Daily Avg 2 = 
IF( 
    NOT ISEMPTY( vw_census_trend1 ), 
    AVERAGEX( VALUES( DimDate1[Date] ), CALCULATE( COUNTROWS( vw_census_trend1 ) ) + 0 ) 
)

Edit: added in more info on setting format strings based on subsequent Q&A