Performance issues with KEEPFILTERS

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