CODEWITHSUNDEEP
powerbidax
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6960

DAX REMOVEFILTERS vs ALL

How to substitute REMOVEFILTERS in the following code with old school functions ALL and VALUES instead? This exercise is just to better understand REMOVEFILTERS. 

CALCULATETABLE (
    -- get all products, in the modified filter context of...
    VALUES ( MyTable[product] ),
    -- no filter on product
    REMOVEFILTERS ( MyTable[product] ),
    -- and under the same parent category
    VALUES ( MyTable[Category] )
)

As far as I can read the hints here that could be possible.

Here goes sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSwpVtJRSiwoyEkF0oZKsTpIwkmJeUAIZJigipfn56QlpRYVVQLZpqhSyRlQcWOweFhqempJYlJOKlgusagovwTIMMKUK8gvSSzJhzsBRS4/LzM/D0ibo1qFw9HILogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}})
in
    #"Changed Type"

Upvotes: 4

Views: 14458

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

REMOVEFILTERS is just an alias of ALL here, so it works just the same.

Basically, ALL returns a table including all rows, ignoring any filters that might have been applied.

However, when ALL is used as a filter argument of CALCULATE or CALCULATETABLE, it behave totally differently: it removes filters from the table and does not return a table.

To alleviate this confusing behavior of ALL, REMOVEFILTERS was introduced to replace ALL when it is used inside CALCULATE.

For more details, you may look at this article. https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

Upvotes: 8

Related Questions