topic Re: Calculating filter only for certain rows in a table in DAX Commands and Tips

Calculating filter only for certain rows in a table

calamistratus — Wed, 15 Apr 2026 09:57:09 GMT

I'm having troblues optimizing fitter for a table of 25K rows.

It contains companies and their items, there are only 100 companies and a very high number of Items.

The filter I want to apply picks top 5 selling companies, but the filter is memory-heavy and the PBI runs out of memory calculating it for 25k rows.

My question is, can I calculate it only for the 100 companies, without repeating this for every item?

Filter calculation:

ShowCompany =
VAR N = SELECTEDVALUE('TopNValues'[Ranks])

VAR TopNBrandN =
TOPN(N,
ALL('unique_brands'[brand]),
[SalesForChosenPeriods],
DESC
)

RETURN
IF(
CONTAINS(
TopNBrandN,
unique_brands[brand],
SELECTEDVALUE('unique_brands'[brand])
) || N = 0,
1,
0
)

Re: Calculating filter only for certain rows in a table

Juan-Power-bi — Wed, 15 Apr 2026 13:05:48 GMT

Hola Amigo

The problem is that your measure runs the TOPN calculation once per row in the visual, so with 25k rows it's running 25k times. The fix is to compute it once and reuse it.

The key trick is to make DAX cache the TopN result instead of recalculating it per row.

Something like this should be much lighter:
daxShowCompany =
VAR N = SELECTEDVALUE('TopNValues'[Ranks])
VAR TopNBrands =
CALCULATETABLE(
TOPN(N, ALL('unique_brands'[brand]), [SalesForChosenPeriods], DESC),
ALL('unique_brands')
)
RETURN
IF(
N = 0 || SELECTEDVALUE('unique_brands'[brand]) IN TopNBrands,
1,
0
)
Using IN instead of CONTAINS is also faster. The main thing though is making sure the TOPN runs against the brand table directly without being re-evaluated in item-level context.