FILTER function using current slicer selection

[cmckinney]

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)

[TomMartens]

Hey @cmckinney ,

you can use the function SELECTEDVALUE(...) to pass/inject the currently selected value into the FILTER function like so:

FILTER(<table>, Column1 = SELECTEDVALUE('tablename'[colum used in  the slicer] , [optional default value]))

 Here you will find some more information about the function: https://dax.guide/selectedvalue/

Hopefully, this provides what you are looking for.

Regards,

Tom

[edhans · Accepted solution]

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]

@edhans Does it work with MAXX as well?

[edhans]

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

[TomMartens]

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]

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]

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

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. 

[cmckinney]

@edhans @TomMartens 

Here is some context and some real data that you can play around with and hopefully make it work. 

Sample file. https://www.dropbox.com/s/gsaucwi6iqj1upv/RulesConditionalFormatDummy.pbix?dl=0

The end goal is to plug the measure into a conditional format on a matrix.

The product of the measure should be a 0 , 1 or 2.

1 = If the hashed value has changed from one rule to the next.

2 = If the rule is new

0 = If there is no change and the rule is not new

In the conditional format 1 will be red, 2 will be blue and 0 will remain black.

The first column in the matrix must always remain black. 

Here is a screenshot of the sample file I made. Notice, the second column is blue. It shouldn't be blue because the previous hash value is not blank and has the same hash value from the previously displayed date.

The only two that should be formatted and are formatted correctly are the red 'hashchange' and the blue 'new rule'.

When the 'Combined Test' publish set is selected the colum turns blue. This shouldn't happen because it is the first column in the table and should be black. 

Hopefully this context helps!