Table with TOP 50 for measure is very slow

[Nick-Singleton]

I have a table with the values "disease" and "filtered patient table".  Filter Patient Table is a measure.  This measure is defined as 

FILTERED PATIENTS TABLE = COUNTROWS(PATIENT).  When the the table is filtered to DISEASE top 50 by FILTER PATIENT TABLE, the dashboard is really slow to load the first time.  It takes around 30 seconds.  I'm trying to figure out how to make this faster.  Here is the DAX I pulled from the performance analyzer.

DEFINE
MEASURE 'PATIENT'[FILTERED PATIENTS TABLE] =
(/* USER DAX BEGIN */
COUNTROWS(PATIENT)
/* USER DAX END */)

VAR __ApplyFilterSQDS0 =
TOPN(
50,
SUMMARIZECOLUMNS(
'Diagnosis'[disease],
"FILTERED PATIENTS TABLE", 'PATIENT'[FILTERED PATIENTS TABLE]
),
[FILTERED PATIENTS TABLE],
0
)

EVALUATE
TOPN(
501,
SUMMARIZECOLUMNS(
'Diagnosis'[disease],
__ApplyFilterSQDS0,
"FILTERED PATIENTS TABLE", 'PATIENT'[FILTERED PATIENTS TABLE]
),
'Diagnosis'[disease],
0
)

ORDER BY
'Diagnosis'[disease] DESC

Please help!

[Anonymous · Accepted solution]

Hi

Please create the following measures:

Ranks = rankx(all(patient[disease]),[FILTERED PATIENTS TABLE])
top 50 := IF(Ranks <= 50,[FILTERED PATIENTS TABLE])

Remove your top 50 from the visual filter

Substitute measure FILTERED PATIENTS TABLE from the visual values with the top 50 and let me know if that improved.

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com