Can the Filter Function be used with Slicers?

Anonymous · ‎07-19-2023

Hi all,

I have a report I'm developing and it works for company level but not when I want to use a slicer to investigate by depot/location.

The background is as follows.

"Sales" is direct calculation based on our weekly import and is working as expected.

"Last Year" is a Dax expression:

Calculate(

SUM(Imports[SALES]),

Filter(

ALL(imports),

Imports[Week No]

=MAX(Imports[Week No])-100

)

So I've added a slicer to this sheet in order to restrict data to "Manchester" but what happens is the sales value as per the imports works fine, but the DAX expression doesn't incorporate the silcer and continues to show the Last Year value for the full company,

(Note: I know there is a time inteligence function for "This Time Last Year" but this won't work with our current data.)

So my question is, are Slicers and DAX expressions compatiable or do I need to change my use of the filter function?

The solution needs to allow for other depot/location data to be selected in the slicer. For example, Should we switch the slicer from Manchester to London, I need the outcome of the DAX expression to update automatically and in accordance with the slicer.

I look forward to hearing a response and developing my user of the filter function, thanks in advance.
Regards,

Chris

Anonymous · ‎07-20-2023

Thanks for your response, so I understand that ALL is the problem. In which case can you advise how I would successfully use a filter to get the outcome I desire.

I've read the provided link and expanded into the filter function, do I need to marry the filter with a RELATED?

Please help?

Thanks in advance.

Regards,

Chris

mlsx4 · ‎07-20-2023

As far as I know, it is something tricky, but you can use a variable to capture the center and use it in your measure.

var _selectedCenter= SELECTEDVALUE(Table[center]) 

return
  Calculate(
      SUM(Imports[SALES]),
        Filter(
            Table [Center]= _selectedCenter,
            Imports[Week No]
            =MAX(Imports[Week No])-100
        )
  )

But the problem of doing something when you want to deal with multiple selection or nothing selected... It may be some solution for sure, but I will need to check how to do it

mlsx4 · ‎07-19-2023

Hi @Anonymous

The problem is that you are using ALL(imports) in your formula, which ignores filters applied (https://learn.microsoft.com/en-us/dax/all-function-dax)

Can the Filter Function be used with Slicers?