# PowerPivot Text Column Filter via a Slicer



## STurner (Nov 11, 2014)

Hi

I’m a new PowerPivot user looking for help.  I’ve been asked to provide a mechanism in a PowerPivot spreadsheet that allows a value selected from Slicer lookup (text value) to filter a particular column in a powerPivot table. The powerPivot table column holds comma separated entries. Rows should be found that contains a match to the slicer selected. Eg

Slicer lookup entries look like:

This
That
Other

PivotTable column to filter on contains row entries like:

1 This,That
2 This
3 Other,That,This
4 That
5 This,Other
etc

Hence, if the slicer selected was 'That', only rows 1, 3 and 4 would be shown in the table after the filter has taken place. I’m using Excel 2013.

Can anyone help with regards a suitable calculated field/DAX query to use?


----------



## RoryA (Nov 11, 2014)

I'm sure there are better ways, but this at least seems to work for single selections in your slicer:

Two tables:
Table1 is your main data table, Header1 contains your column of comma separated items.

Table2 has one column, Filter, with the various filter values.

Create a new measure for table1:

=CALCULATE(COUNTA(Table1[Header1]),filter(Table1,IF(COUNTROWS(Table2)=1,search(values(Table2[Filter]),Table1[Header1],1,0)>0,TRUE)))

Apply a filter to the row fields to only show values greater than 0, then add your Slicer to table 2.

Edit: I think this measure will work with multiple slicer selections:

=CALCULATE(COUNTA(Table1[Header1]),filter(Table1,COUNTAX(FILTER(Table2,search(Table2[Filter],Table1[Header1],1,0)),Table2[Filter])>0))


----------



## STurner (Nov 13, 2014)

Rory

That's great, just what I needed to get me started. Thanks.


----------



## RoryA (Nov 13, 2014)

Glad to help. Good for me to start learning this stuff!


----------



## itsn78 (Feb 21, 2017)

Hi RoryA

can you please share an excel demo for the solution mentioned above ?

I am not able to implement it in excel.

Please

Thank You


----------



## RoryA (Feb 21, 2017)

Which version of Excel?

PS Please don't hijack other people's threads trying to get my attention (I've deleted your other post), or PM me - I just delete all PMs like that or I'd be swamped with them.


----------



## itsn78 (Feb 21, 2017)

sorry for the post in the other thread, I wont do it again.

I need a solution in excel 2013 / 2016 version

please share a demo excel file of the above mentioned solution..

Thanks a ton.


----------



## RoryA (Feb 21, 2017)

OK, there's a 2016 demo file here: https://1drv.ms/x/s!AsDYl-5bgVQWgP94F51lkx2bmLcfRQ


----------



## itsn78 (Feb 22, 2017)

RoryA thanks for the excellent solution that you have provided. its like a breakthrough in calculations of excel and sharepoint related data !

Thanks a ton !!!


----------

