Performance issues with KEEPFILTERS

[fjjpeeters1976]

I have performance issues with my report due to the use of KEEPFILTERS

let me explain:

- I have to add up very specific numbers on different account hierarchies for different scenarios. The calculation works correctly and gives me the correct number

- Now I want to show this amount in a matrix visual with the possibility to drill down through the hierarchies.

- This means I have to keep the filter contect of the accounts otherwise it is giving me the same numbers on all rows

- so I introduced in the Dax code the keepfilters function, and as it needs to keep the filters on different hierarchy levels I cannot use the keepfilters function on a specific column but I have to use the full account hierarchy structure. This is slowing my calculation down significantly.

I have put a screenshot of the view I want to have below, and also the DAX code for the measure.

Is there a way to optimize this function to get it to perform better?

Energy Scen1 =

VAR Scen1 =     SELECTEDVALUE ( Scenario1[Level0] )

VAR Energy =

    CALCULATE (

        [Amount],

               DimAccountDetailed[Account_Level_6] = "411300 - Sales of energy"

            || DimAccountDetailed[Account_Level_6] = "412400C - Other net sales, IC"

            || DimAccountDetailed[Account_Level_5] = "522100 - Electricity, var."

            || DimAccountDetailed[Account_Level_5] = "522300 - Other energy costs, var."

            || DimAccountDetailed[Account_Level_6] = "523000S - Fuels, supplies and Energy,

        DimAccountDetailed[Account_Level_6] = "524000S - Other variable costs"

            || DimAccountDetailed[Account_Level_6] = "411200S - Sales of semi-finished and finished products"

            || DimAccountDetailed[Account_Level_7] = "511000S - MATERIAL COGS, RAW MATERIAL VALUE"

            || DimAccountDetailed[Account_Level_6] = "411600 - Other net sales"

            || DimAccountDetailed[Account_Level_6] = "521000S - Fuels and supplies total, Ext, var."

    )

VAR Result =     CALCULATE ( [Energy],         DimScenarioDetailed[Level0] = Scen1,         KEEPFILTERS(DimAccountDetailed))

RETURN

    Result

[maruthisp · Accepted solution]

Hi fjjpeeters1976, 

In Power BI, when you need to calculate numbers that are dependent on multiple levels of hierarchy and you want to keep the filter context for each drill-down in a matrix.

Try to do the steps below:

1. Create a calculated column
IsEnergyRelevant =
SWITCH(
TRUE(),
DimAccountDetailed[Account_Level_6] IN {
"411300 - Sales of energy",
"412400C - Other net sales, IC",
"523000S - Fuels, supplies and Energy",
"524000S - Other variable costs",
"411200S - Sales of semi-finished and finished products",
"411600 - Other net sales",
"521000S - Fuels and supplies total, Ext, var."
}, 1,
DimAccountDetailed[Account_Level_5] IN {
"522100 - Electricity, var.",
"522300 - Other energy costs, var."
}, 1,
DimAccountDetailed[Account_Level_7] = "511000S - MATERIAL COGS, RAW MATERIAL VALUE", 1,
0
)

2. Create a measure to FILTER
Energy Scen1 =
VAR Scen1 = SELECTEDVALUE(Scenario1[Level0])
RETURN
CALCULATE(
[Amount],
DimAccountDetailed[IsEnergyRelevant] = 1,
DimScenarioDetailed[Level0] = Scen1
)

What does happend with the above approach:

1. Column-based filtering is highly optimized in the VertiPaq engine, much more so than KEEPFILTERS on an entire table or complex OR (||) logic.
2. The filter context at each level in your matrix (Account Level 5, 6, 7) is naturally preserved by the matrix visual. You do not need to manually keep all filters with KEEPFILTERS on the whole table.
3. You turned a row-wise scan into a dictionary lookup on a single flag column.

please check it and let me know if you have any questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X