How to search custom (User defined) Value in PowerPivot

[tusharmehta]

Hello All,

I am having query on power pivot search.

In below mentioned example I have two tables.

In first (TransTable) Table I have two columns Date and Remarks.

In second ( CustomValueTable) table I have only column Remarks.

Here is my question,can we able to filter records base on second table UDFvalue (through slicer) on power pivot, if yes then how ?


TransTable

[TABLE="class: grid, width: 175"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2014[/TD]
[TD]Tushar[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2014[/TD]
[TD]Rajesh[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2014[/TD]
[TD]Alpesh[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2014[/TD]
[TD]Tushar[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2014[/TD]
[TD]Amber[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2014[/TD]
[TD]Henil[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2014[/TD]
[TD]Yukta[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2014[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2014[/TD]
[TD]Mehta Rajiv[/TD]
[/TR]
</tbody>[/TABLE]


CustomValueTable

[TABLE="class: grid, width: 105"]
<tbody>[TR]
[TD]UDFValue[/TD]
[/TR]
[TR]
[TD]Rajiv[/TD]
[/TR]
[TR]
[TD]Tushar[/TD]
[/TR]
[TR]
[TD]Henil[/TD]
[/TR]
</tbody>[/TABLE]

Sample Excel File can be download from below mention url:

https://drive.google.com/file/d/0B_c8gU3TDT4rWlpoYnVUSjBEN3c/edit?usp=sharing

 

[scottsen]

Certainly. Just make sure you have a relationship between the 2 columns, and it will "just work".

 

[tusharmehta]

Hello Scottsen,

Thanks, I had created relationship between the two tables.

Remarks of Transtable and UDFValue on CustomValueTable

And after that I had created power Pivot table in below describe format (reports) but because of some issue when I click on UDFValue slicer value Row Label value don't get filtered.

Furthermore, I want to understand that, if UDFValue contain not exact words still do you think Remarks value will get filter base on matching words ?

Example on Remarks there is name call Mehta Rajiv where in UDFValue there is value Rajiv Only, goal here is to filter the records on Remarks with any matching words.

SidebySide when I was looking into excel book, I had look into all function and found that on excel there is function call search which does the job are you aware of that, do you think we should look into that if we can't work with DAX ?


---------------------------
Pivot Table structure
---------------------------
Row Label

Remarks

Slicer
UDFValue

--------------------------------------------------------------

[TABLE="class: outer_border, width: 92"]
<tbody>[TR]
[TD]Row Labels[/TD]
[/TR]
[TR]
[TD]Alpesh[/TD]
[/TR]
[TR]
[TD]Amber[/TD]
[/TR]
[TR]
[TD]Henil[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Mehta Rajiv[/TD]
[/TR]
[TR]
[TD]Rajesh[/TD]
[/TR]
[TR]
[TD]Tushar[/TD]
[/TR]
[TR]
[TD]Yukta[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table_grid, width: 105"]
<tbody>[TR]
[TD]UDFValue[/TD]
[/TR]
[TR]
[TD]Rajiv[/TD]
[/TR]
[TR]
[TD]Tushar[/TD]
[/TR]
[TR]
[TD]Henil[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

The exact matches should certainly work as described.

Doing partial matching in a slicer would be tricky. My gut is... it will be possible, but I'm gonna have to think and play with that a bit. Probably won't get to it for a day.

 

[scottsen]

I am not commenting if I think this is a good & sane idea, or implementation But I was able to get it to work...

I created a "Disconnected Slicer" (powerpivotpro term, google on this site), with the short names... in my cash "Dan" and "Fish".

My full table of names:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Blue Fish[/TD]
[/TR]
[TR]
[TD]Dan Quayle[/TD]
[/TR]
[TR]
[TD]Dan Rather[/TD]
[/TR]
[TR]
[TD]Daniel Boon[/TD]
[/TR]
[TR]
[TD]Daniel Radcliffe[/TD]
[/TR]
[TR]
[TD]Daniel's Broiler[/TD]
[/TR]
[TR]
[TD]Jeff Daniels[/TD]
[/TR]
[TR]
[TD]Red Fish[/TD]
[/TR]
[TR]
[TD]Thing 1[/TD]
[/TR]
[TR]
[TD]Thing 2[/TD]
[/TR]
</tbody>[/TABLE]

Measure I wrote:
SelectedName :=IF(COUNTROWS(PartialNames) = 1, FIRSTNONBLANK(PartialNames[Name], 1))

To "harvest" the name out of my disconnected partial name slicer.

IsPartialMatch := IF (ISERROR(FIND([SelectedName], VALUES(FullNames[Name]))), 0, 1)

Return 1 if I find a match between the selected name, and the FullName that I presumably have one rows of my pivot table.

Then, on my pivot table I just use a "Value Filter"... saying to only Show Items for "IsPartialMatch = 1".

That's pretty much it!

 

[dhExcel]

See: Guest Post: Calculating a Sum Based on a List Criteria « PowerPivotPro

 

[tusharmehta]

scotten,

Thanks,

To understand your concept & Example, I had re-wrote your example on excel sheet.

Outcome of result is almost the way I am looking.

So, I will test similar concept on my example and see if any question I will post the response here.

 

[tusharmehta]

See: Guest Post: Calculating a Sum Based on a List Criteria « PowerPivotPro

Appreciate dhExcel for your suggestion