CODEWITHSUNDEEP
powerbidax
Paul Cavacas
Paul Cavacas

Reputation: 4454

PowerBI Filter Context Problems

I have a measure setup in Power BI, that is not behaving as I would expect and I'm trying to figure out what is missing here. The measure that I have defined is fairly straightforward and seen below.

Test Filter Removal = 
CALCULATE(
    SELECTEDVALUE('Report Options'[Operation Costs], "Costs "),
    REMOVEFILTERS('Report Options'),
    FILTER('Report Options', 'Report Options'[Operation Costs] = "Costs at Current Year Rates")
)

The table 'Report Options'[Operation Costs] has 2 options in it "Costs at Actual Year Rates" and "Costs at Current Year Rates". The context on the page for this report is that the Costs at Actual Year Rates is selected, but for this measure I want it to report Costs at Current Year Rates.

The full measure is more complicated then this, but the whole problem of the more complicated measure comes down to the fact that this part of the measure is returning "Costs " in this example, which is the default value being used in the SelectedValue function.

What I don't understand about this is that the filter specified is should be specifically setting it to Costs at Current Year Rates. I'm sure that the problem has to do with context, but I'm not seeing what it is.

Upvotes: 0

Views: 68

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40264

Because all of the filter arguments in CALCULATE are evaluated with AND logic, REMOVEFILTERS isn't really doing any work here because your FILTER argument is a subset of that larger table.

I suspect you want something more like this:

Test Filter Removal = 
CALCULATE (
    SELECTEDVALUE ( 'Report Options'[Operation Costs], "Costs " ),
    'Report Options'[Operation Costs] = "Costs at Current Year Rates"
)

which is equivalent to

Test Filter Removal = 
CALCULATE (
    SELECTEDVALUE ( 'Report Options'[Operation Costs], "Costs " ),
    FILTER (
        ALL ( 'Report Options'[Operation Costs] ),
        'Report Options'[Operation Costs] = "Costs at Current Year Rates"
    )
)

Note that without the ALL (like in your DAX), the table is evaluated within the filter context and is thus affected by the slicer and hence returns an empty table when the slicer doesn't include "Costs at Current Year Rates" for that column.

Upvotes: 2

Related Questions