Limit Data WITHOUT TOPN

[dorku]

Hello everyone,

I have a question about a table (drill-through) I use in my dashboard.

The table is based on a fact table (with one item per row) and can dynamically display anywhere between 5 rows to 5 million rows. It joins with 7 different dimension tables and includes 15 columns.

I’m trying to improve the performance of this table and was thinking about implementing the following logic:

• By default, show only 1,000 items ordered by the latest datetime.
• Add a button to allow users to load all the data if needed.

The problem is that using TOPN doesn’t seem to help (as far as I understand) because the engine still loads all the data first and only then filters it down to the top 1,000 rows. I tested this, and there was no noticeable improvement in performance.

I’m not sure how to implement this in DAX. I’m working with a Tabular model using a live connection, and the solution needs to remain dynamic and interactive.

Do you have any suggestions for how to achieve this?

Thanks in advance!

[Anonymous · Accepted solution]

Hi, @dorku 

You can try the following methods.

Sample data:

Measure = Var _rank=RANKX(All('Table'),CALCULATE(MAX('Table'[Date])),,DESC,Dense)
RETURN
IF(_rank<=10,1,0)

Place this measure on filters on this visual and set it equal to 1.

At this point, the top ten data by date are displayed by default. If you choose to clear the filter, all data will be displayed.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

By default, show only 1,000 items ordered by the latest datetime.
Add a button to allow users to load all the data if needed.

The visual already does this for you automatically.  It pages results in chunks of 500 rows.

Ironically, the DAX code for that uses TOPN(501,...) if you want it or not 🙂