Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Can the Filter Function be used with Slicers?

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

DAX
Filter Function
slicers

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      mlsx4
      Icon for Memorable Member rankMemorable Member
      3 years ago

      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