PowerPivot Text Column Filter via a Slicer

[STurner]

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]

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]

Rory

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

 

[RoryA]

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

 

[itsn78]

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]

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]

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]

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

 

[itsn78]

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 !!!