Visualisation poor performance

[Anonymous]

Hello, 

I'm trying to understand why a report I am working on has such poor performance. The reason it is confusing is that it uses two measures which search thousands of rows of text (i would expect this to be slow) - however, one measure is built using another (i.e one provides a distinctcount and the other uses that distinct count to provide a cummulative total.

However, when i try to load them it is the first one (i.e the one that does not provide the cummulative total) that is slow and the other (which uses it) loads very quickly. 

Is it the choice of visuals I am using?

Here are the two measures:

No1 (Slow)

OR CN Mention Count = VAR s1 = MAX ( 'Text Search (Or1)'[Text Search (Or1)] )

VAR s2 = MAX ( 'Text Search (Or2)'[Text Search (Or2)])

VAR s3 = MAX ( 'Text Search (Or3)'[Text Search (Or3)])

RETURN CALCULATE ( DISTINCTCOUNT ( 'All Casenotes'[Casenote Record ID]), FILTER ( 'All Casenotes', CONTAINSSTRING ( [Notes on session / activity], s1 ) || CONTAINSSTRING( [Notes on session / activity], s2 ) || CONTAINSSTRING( [Notes on session / activity], s3 ) ) )

No 2 (quick)

OR Cummulative mention count = CALCULATE([OR CN Mention Count],filter(all('All Casenotes'[Date of session or activity]),'All Casenotes'[Date of session or activity]<=max('All Casenotes'[Date of session or activity])))

Any advice would be appreciated !

Thanks 

[Anonymous]

Thanks very much for your replies. 

I changed the forumla to use Counttable but the performance is still very slow on this measure: 

My model is like this quite simple it just has a table with Casenotes (long text fields) which is linked to a table with project information, which is then linked to a table with Client information.

However, I wouldn't have thought any tweaks to the model would matter as this measure is only using one table?

[AilleryO]

Hi,

You can try as well to replace your FILTER() functions by CALCULATETABLE() functions.

More information on that : 

FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI

It's an easy and fast change, and normally shoudl give you better performance.

For a more detailed answer we need to know more on your model and relationships.

Have a nice day

[StefanoGrimaldi]

first I would suggest you identify your performace exactly where its slacking, 

opne the panels view and active the performace analyzer panel, run a report refresh from scrap (clean the cache before hands on the power bi desktop optiones and settings)

them search those components, visuals, measure with the most time to exacute, 

them you can copy the query or measure of those and take them to the Dax Studio external tool

there you can try each query, each measure and the data model per table to see what part its performing poorly