Forum Discussion
Calculating filter only for certain rows in a table
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
)
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.Hi! calamistratus
You can try this
ShowCompany :=VAR N = SELECTEDVALUE ( 'TopNValues'[Ranks], 0 )VAR BrandRank =RANKX(ALL ( 'unique_brands'[brand] ),[SalesForChosenPeriods],,DESC,DENSE)RETURNIF ( N = 0 || BrandRank <= N, 1, 0 )
4 Replies
Hi! calamistratus
You can try this
ShowCompany :=VAR N = SELECTEDVALUE ( 'TopNValues'[Ranks], 0 )VAR BrandRank =RANKX(ALL ( 'unique_brands'[brand] ),[SalesForChosenPeriods],,DESC,DENSE)RETURNIF ( N = 0 || BrandRank <= N, 1, 0 )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.Thank you for reaching out to the Microsoft Fabric Forum Community.
Juan-Power-bi AnkitKukreja Thanks for the inputs.
I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.
Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.
