Create a measure that states whether value has been filtered by a slicer or not in a table.

[Anthony_W]

Hi,

I am trying to create a measure that indicates (e.g. Yes or No) whether the current row of values in a Table has been selected by a slicer on the same report page. 

For example, I have the following table on a report page. There is also a slicer for 'Section' where you can chose from A, B, C, D, or E. More than one Section can be selected. If I selected Section A in the slicer, I want all rows for Section A to show Yes in the Selected by Slicer Column. The other rows should show No.

I have attempted possible solutions using selectedvalue, but they all end up with the same value for all of the rows. I believe I am not calculating the context correctly in my dax code.  

Note that I changed the interaction between the slicer and the table such that the table is not filtered when a slicer value is selected (otherwise the not selected Section rows would disappear).

Ultimately what I am trying to do is pass a large dataset into a custom R visual. The large dataset will contain a column stating whether the row is the filtered dataset or not. The visual will use all of the data, as well as the data selected by the user using slicers etc to generate the visualisation.

Any ideas on how to show whether the row values in a table have been filtered by slicers or not? 

 Thanks in advance for your help.

Year	Section	Part	Tonnes	Selected by Slicer
2020	A	1	167	Yes
2020	A	2	258	Yes
2020	B	1	476	No
2020	B	2	12	No
2020	C	1	246	No
2020	D	1	106	No
2020	D	2	331	No
2020	E	1	460	No
2021	A	1	325	Yes
2021	A	2	391	Yes
2021	B	1	262	No
2021	B	2	424	No
2021	C	1	147	No
2021	D	1	332	No
2021	D	2	281	No
2021	E	1	108	No
2022	A	1	207	Yes
2022	A	2	499	Yes
2022	B	1	299	No
2022	B	2	184	No
2022	C	1	203	No
2022	D	1	292	No
2022	D	2	485	No
2022	E	1	423	No
2023	A	1	30	Yes
2023	A	2	339	Yes
2023	B	1	465	No
2023	B	2	280	No
2023	C	1	286	No
2023	D	1	487	No
2023	D	2	139	No
2023	E	1	280	No

[Greg_Deckler]

First, you will need a disconnected table of your slicer values. Let's call that table Sections and you have a Section column in that table with values like:

A

B

C

D

Now, you create a measure like this:

Measure = 
  VAR __SlicerValues = 'Sections'
RETURN
  IF(MAX('Table'[Section]) IN __SlicerValues,"Yes","No")

[Anthony_W]

Thanks for the suggestion Greg.

see below for a link to PBIX file to work this out. I added your measure but it didn't work. Any other ideas or did I do something wrong in implementing your solution?

https://mylecnet-my.sharepoint.com/personal/anthony_williams_abmylec_com_au/_layouts/15/onedrive.asp...

[amitchandak · Accepted solution]

@Anthony_W ,

Edit interaction was the issue. I corrected it. Done little change in formula. Both formula working now

[Anthony_W]

Thanks Amitchandak,

I checked the updated report and it does work. I updated the report to include 2 different slicers, and this works too.

Thanks.

[amitchandak]

Try like

measure =
var _max = maxx(allselected(Table),Table[Section]) // or take Today()
return
calculate(if(firstnonblank(Table[Section],blank()) ="_max","Yes","No"),all(Table))

[Anthony_W]

Thanks for the suggestion Amitchandak.

see below for a link to PBIX file to work this out. I added your measure but it didn't work. Any other ideas or did I do something wrong in implementing your solution?

https://mylecnet-my.sharepoint.com/:u:/g/personal/anthony_williams_abmylec_com_au/EbGrr1JyptZBsi3Pof...