Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bswylie
Helper I
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):

 

Source.PNG

 

 

 

 

 

 

 

 

 

 

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:

 

Costs Selected.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

@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 !

View solution in original post

4 REPLIES 4

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.

Hi @Datatouille

 

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

 

 

@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 !

@Datatouille

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.