0

I have a powerpivot table that contains 2 columns: Column 1 contains strings. Column 2 contains comma delimited strings.

I would like to be able to display all the rows from column 1 when rows from column 2 contains the selection from a filter or slicer. For example:

String Values
ABCD A,A,B
EFGH A,C
if A is selected I would display both rows, if B is selected I would display only row 1...etc.

I know I can split the records - but this is not practical for me - the above is only the top of the iceberg. VBA is out of the question since this will published in SharePoint. Anybody has an idea on how I could do that ? Thanks.

user1238760
  • 61
  • 2
  • 6

1 Answers1

1

I found the solution in a blog from Javier Guillem:
http://javierguillen.wordpress.com/2012/02/10/simulating-an-approximate-match-vlookup-in-powerpivot/
If in my example the name of the table is "facts", I create a second unlinked table called dimRef that I populate with all possible values that I am interested to match: A,B,C,D...etc.
Then I define the measure M as:
M:=If ( Hasonevalue(facts[Values] ), Calculate ( LASTNONBLANK (dimRef[String], 1 ), Filter ( dimRef, SEARCH(dimRef[String],Values(facts[String]),1,0) > 0 ) ) ) I can then use the string column of the facts table and the measure in a pivot table and use dimRef as a selector. If filters the row as per the selection.

enter image description here
One small detail: the measure is not available in PowerView...Anybody knows why ?

user1238760
  • 61
  • 2
  • 6