Calculating filter only for certain rows in a table

[calamistratus]

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
    )

[Juan-Power-bi · Accepted solution]

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.

[v-priyankata]

Hi @calamistratus 

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.

[v-priyankata]

Hi @calamistratus 

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.

[AnkitKukreja · Accepted solution]

Hi!   @calamistratus 

You can try this

ShowCompany :=

VAR N = SELECTEDVALUE ( 'TopNValues'[Ranks], 0 )

VAR BrandRank =

    RANKX(

        ALL ( 'unique_brands'[brand] ),

        [SalesForChosenPeriods],

        ,

        DESC,

        DENSE

    )

RETURN

IF ( N = 0 || BrandRank <= N, 1, 0 )

[Juan-Power-bi · Accepted solution]

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.