Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHey @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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYes, it returns a table, so anywhere you need a table, like MAXX() does, it should work
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHey @cmckinney
as @edhans
Personally, I would not use ALLSELECTED as this is one of the most complex functions, and I like my DAX statements simple 🙂
Regards,
Tom
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
Is there a way to apply either the SELECTEDVALUE() expression or the ALLSELECTED() expression in the following function?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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!
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |