ALLSELECTED Vs KEEPFILTERS : Which is better in terms of performance?

[sreenathv]

Hi @Greg_Deckler, @ImkeF , @TomMartens 

I have a quick question based on the following scenario:

1) I need a measure with specific filters in it (example Qty * RateInLocalCurrency > 50000)

2) But users would be slicing the visual based on slicers outside the visual. (example: Qty > 200 or DocumentCurrency = USD etc...)

3) I could write the measure in two ways.

One using ALLSELECTED()

Measure  = 
CALCULATE(
    <Something>,
    FILTER(
        ALLSELECTED(<SomeColumnsWithFiltersOutsideTheVisual>),
        <FilterCondition>
    )
)

Alternatively using KEEPFILTERS()

Measure =
CALCULATE(
    <Something>,
    KEEPFILTERS(
        FILTER(
            ALL(<SomeColumnsWithFiltersOutsideTheVisual>),
            <FilterCondition>
        )
    )
)

I tried both and they are giving the same result.

I am just curious to know, is there any best practice or optimal suggestion on which will be better? The ALLSELECTED() version or KEEPFILTERS() version?

[amitchandak]

You can refer for allselected: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

[Greg_Deckler]

Well, I'm more about what works and makes sense to you, the author. What is more natural to you in terms of your thinking and your patterns? So that when you revisit the measure a year from now you aren't trying to figure out your own code and how it works, that it makes sense to you.

In your case, the ALLSELECTED pattern makes more sense to me, but that's just my opinion. I personally find the KEEPFILTERS pattern unintuitive. The purpose of ALLSELECTED is to remove filters inside the query but keep those outside of the query so if that fits the bill, that seems like the best way to go. Otherwise you are starting with the entire table and then filtering down by re-implementing your filters which seems a bit asinine to me. But, I'm not the Power BI thought police.

Have you tried the Performance Analyzer to see if there is any difference in performance? Would be interested to know if there is a difference.

Might want to read this:

https://www.sqlbi.com/articles/using-keepfilters-in-dax/

[sreenathv]

Hi @Greg_Deckler 

Thanks for the link to Marco Russo (@marcorusso )'s article.

I have used the performance analyzer and could not find any significant difference between ALLSELECTED version and the KEEPFILTERS version. But the SQLBI article says that whenever there is a multi-column predicate in the filter, it is better to use KEEPFILTERS than adding the filters explicitly using table functions. Apart from the semantics, the DAX engine will handle the KEEPFILTERS version better.

So the conclusion is that maybe,  KEEPFILTERS is closer to perfection than ALLSELECTED in this kind of scenario.

[marcorusso · Accepted solution]

There is a possible difference - assuming the columns are *not* used in the visual and that you are using more than one column in the filter.

By using ALLSELECTED, the cardinality of the iteration could be smaller if there are existing filters outside of the visual.

By using KEEPFILTERS/ALL, the cardinality is always that of the columns reference in the entire table.

In a specific report, ALLSELECTED could be faster if there are filters outside.

However the KEEPFILTERS/ALL version could better reuse the filter in multiple queries thanks to the storage engine cache.

If the cardinality is relatively large and the filters could strongly reduce them, ALLSELECTED could be faster - keeping in mind you might have issues using the measure with ALLSELECTED in other measures that have iterators - in that case, KEEPFILTERS/ALL is safer to use.

As usual, it depends.

[sreenathv]

Hi @marcorusso ,

Thanks a lot for the explanation. When I posted this question, I was not even sure about what kind of answer to expect. But your prompt response is extremely insightful.

Sometimes, apart from the reports I publish,  other users in my organization will be creating other reports by connecting to the PowerBI Dataset that I publish and share with them. In that scenario, I would not have any control over what visuals and filters they will be adding in their reports based on my data model and dataset. Sometimes, I will be using the "Analyse with Excel" option and consuming the reports in excel from Power BI Datasets based on ad-hoc needs.

Moreover, whenever there is a 'CALCULATE', there are more things going on behind the scenes like context transitions, filters, nested CALCULATE when we reuse the measures, etc... and I had to keep thinking about more things that are likely to happen in the background than just the result of the formula temporarily. 

With your explanation and the perspective, I will be more confident about the reusability of my measures in various reporting scenarios. 

Once again thanks a lot and it took nearly 20 minutes of contemplation to actually understand the implication of what you said in your response.