cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## ALL Function - allow filter for columns but still apply ALL for a specific measure

I seem to have a situation where I both need to apply a filter based on a column called "metric", but also have to encapsulate the measure to not filter the source table based on that same column. Let me demonstrate... (it feels like a really noob question but I'm just not able to think the right way around this, so would be really grateful for any pointers...)

I've loaded a file to... https://dl.dropboxusercontent.com/u/78036039/Slicer%20with%20Measures%20%26%20Columns.pbix

Consider a simple measure being

Profit = Revenue - Costs

The source table looks something like this (data like this by necessity):

So my calculation for the measure [Profit] is:

Profit = CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Revenue")) - CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Costs"))

This works great - until I have a dashboard where I filter visuals using a slicer on either revenue or costs:

As you can see - when I select "costs" in the slicer, the profit is calculated as P = [0] - [Costs], because the revenue is filtered out from the source table (giving a value of zero), and profit is simply negative costs.

What I'd like to obviously have is to filter the table to update the top visual (show either revenue or costs in top graph) but apply the ALL function for the Profit measure.

I've tried something like the following (which feels intuitively incorrect anyway, and doesn't work either):

Profit = CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Revenue"), ALL('SourceData'[Metric])) - CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Costs"), ALL('SourceData'[Metric]))

What's the correct way of handling a situation like this? Again, sorry for the newbie question.

1 ACCEPTED SOLUTION
MVP

@bswylie

Since your slicer comes from Dim_Metric Table, you have to apply your filter on Dim_Metric Table to benefit from filter propagation !

[Costs]= CALCULATE( [Total Amount]  , Dim_Metric[Metric] = "Costs")

[Revenue] = CALCULATE( [Total Amount] , Dim_Metric[Metric] = "Revenue")

Please accept as a solution so everyone can benefit from our discussion !

4 REPLIES 4
MVP

Hi @bswylie

There is no newbie question !

In this case, you don't need to use filter function.

Try this:

[Total Amount]= SUM(Sales[Value])

[Costs]= CALCULATE( [Total Amount]  , Sales[Metric] = "Costs")

[Revenue] = CALCULATE( [Total Amount] , Sales[Metric] = "Revenue")

[Profit] = [Revenue] - [Costs]

Place [Total Amount] in the "value" section of your first chart - It will automaticaly adjust to filter context and display revenue or costs (based on the slicer selection).

Place [Profit] in the "value" section of your second chart.

Why is this working ?

Because CALCULATE ( [Total Amount] , Sales[Metric] = "Costs") is equivalent to

CALCULATE( [Total Amount] , Filter( All(Sales[Metric]) , Sales[Metric] = "Costs")

It overrides the user's choice (e.g ignores filter context) and forces Sames[Metric] to be equal to "Costs" no matter what the user has chosen (or not chosen).

{Same logic for [Revenue] Measure with "Revenue" filter}

As @MattAllington suggests here, I broke my measures into interim parts. It makes it easier to read, understand and update your calculations.

Helper I

Thanks so much for the response.

Unfortunately this doesn't seem to work? I still get the same outcomes when selecting either Revenue or Cost metric in the slicer.

https://dl.dropboxusercontent.com/u/78036039/Slicer%20with%20Measures%20%26%20Columns%20-%20v2.pbix

Cheers,

Brian

MVP

@bswylie

Since your slicer comes from Dim_Metric Table, you have to apply your filter on Dim_Metric Table to benefit from filter propagation !

[Costs]= CALCULATE( [Total Amount]  , Dim_Metric[Metric] = "Costs")

[Revenue] = CALCULATE( [Total Amount] , Dim_Metric[Metric] = "Revenue")

Please accept as a solution so everyone can benefit from our discussion !

Helper I

@Datatouille

Thanks so much, this works great. Really appreciate it.