Forum Discussion

cmckinney's avatar
cmckinney
Helper IV
5 years ago
Solved

FILTER function using current slicer selection

Is it possible to pass the current selection of a slicer into a FILTER function in a measure?    For example: FILTER(<table>, Column1 = Current Slicer Selection)
Need Help
  • edhans's avatar
    edhans
    5 years ago

    You can use ALLSELECTED([ColumnName]) It is designed to work with slicers. It is intended to work in a CALCULATE() function, so you really don't need the FILTER() function. 

cmckinney's avatar
cmckinney
Helper IV
5 years ago

edhans Does it work with MAXX as well?

 

edhans's avatar
edhans
Community Champion
5 years ago

Yes, it returns a table, so anywhere you need a table, like MAXX() does, it should work

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey cmckinney

     

    as edhans

    • SELECTEDVALUE(...) is good to capture a single value and just a single value
    • VALUES(...) allows capturing multiple selected values from a slicer
      • you have to use VALUES inside the FILTER function like so
        FILTER( t , t[c] IN VALUES())
        As VALUES(...) returns a table it can also be used as the table parameter in one of the table iterator functions like MAXX

      • Personally, I would not use ALLSELECTED as this is one of the most complex functions, and I like my DAX statements simple 🙂

         

        Regards,

        Tom

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    And TomMartens is correct, SELECTEDVALUE() returns a scalar (single) value.

     

    He is better at DAX than I am, so I'll let him decide which is better for a given scenario. Both will work, but one may be better than another for a specific need.

    Note that ALLSELECTED() will remove existing filters in the expression but keep those from outside sources, like the slicer. SELECTEDVALUE() will not, so you may get nulls in certian filter contexts. 

    • cmckinney's avatar
      cmckinney
      Helper IV
      5 years ago

      edhans TomMartens 

       

      Is there a way to apply either the SELECTEDVALUE() expression or the ALLSELECTED() expression in the following function?

       

      VAR __PreviousDate = MAXX(FILTER(ALL(Sheet1),[Analysis Run]<__CurrentDate),[Analysis Run])
       
      The column that is associated with the slicer that I want to filter by is called "Publish Set"
      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        This should work. You changed the sign. ALLSELECTED won't work with greater than/less than logic. It only keeps exactly what was returned by the slicer.

         

         

        Test =
MAXX(
    FILTER(
        ALL( Sheet1 ),
        [Analysis Run]
            < SELECTEDVALUE( [Field] )
    ),
    [Analysis Run]
)

         

        I'd want real data to play with given you are embedding measure in another measure.... Context transition and all of that. 😁

        Caution on SELECTEDVALUE() - it only works on one value. If more than one is selected, you will either get blank, or an alternate result you can supply i the optional 2nd parameter. You will need to gracefully handle that scenario unless you lock the slicer to a single selection in its settings.