Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Table with TOP 50 for measure is very slow

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.
 
powerbi.PNG
 
 

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!

 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors