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!

[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