locked
SQL Server "Like Clause" Search in PP RRS feed

  • Question

  • Hello All,

    My senario is like this,

     

    I have a slicer with values say (apple, Banana, etc) and i have a columns that has those keywords a part of it. up on selecting those slicer value i want to display related records in the PowerPivot. Is it possibe? If yes could you plz share. Thanks.

    Wednesday, September 14, 2011 12:46 PM

Answers

  • Hi Nirml,

    This can be achieved by combining the VALUES() function (which will let you grab the selected value in the slicer) and the SEARCH() text function.

    As an example, create a table called 'Categories'. It has three values:

     

    Categories

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

    Banana

    Apples

    Blueberries

     

     

    This table will be used by the slicer.  Now create another table called 'Data' with two colulmns:

     

    Data                                               Values

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

    JustOneBanana                              1

    LotsOfApples                                  2

     

    Import both tables in PowerPivot.  Do not create any relationships in the data model.  Now,create a slicers using the data on the 'Categories' table.

     

    Create one measure called 'SelectedCategory' with this definiton:

    =IF(
    	COUNTROWS( VALUES( Categories[Categories]) ) = 1, 
    	CALCULATE(   VALUES( Categories[Categories] ) )
    	, BLANK()
    )


    If you place it in the pivot table, you will be able to see the value selected on the slicer.  With this we can now move on to the measure that will do the job.  Add a new measure called 'FilteredValue':

    =CALCULATE( 
    	SUM( Data[Value] ) , 
    		FILTER(
    			Data, 
    			IFERROR(SEARCH([SelectedCategory],Data[Data]), -1) > -1 
    		)  
    	)


    The output of this measure will be the associated number on the Data table based on a text search using the selected slicer value. 

     

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    • Proposed as answer by Challen Fu Thursday, September 15, 2011 6:48 AM
    • Marked as answer by Challen Fu Thursday, September 22, 2011 9:57 AM
    Wednesday, September 14, 2011 6:34 PM
    Answerer

All replies

  • Hi Nirml,

    This can be achieved by combining the VALUES() function (which will let you grab the selected value in the slicer) and the SEARCH() text function.

    As an example, create a table called 'Categories'. It has three values:

     

    Categories

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

    Banana

    Apples

    Blueberries

     

     

    This table will be used by the slicer.  Now create another table called 'Data' with two colulmns:

     

    Data                                               Values

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

    JustOneBanana                              1

    LotsOfApples                                  2

     

    Import both tables in PowerPivot.  Do not create any relationships in the data model.  Now,create a slicers using the data on the 'Categories' table.

     

    Create one measure called 'SelectedCategory' with this definiton:

    =IF(
    	COUNTROWS( VALUES( Categories[Categories]) ) = 1, 
    	CALCULATE(   VALUES( Categories[Categories] ) )
    	, BLANK()
    )


    If you place it in the pivot table, you will be able to see the value selected on the slicer.  With this we can now move on to the measure that will do the job.  Add a new measure called 'FilteredValue':

    =CALCULATE( 
    	SUM( Data[Value] ) , 
    		FILTER(
    			Data, 
    			IFERROR(SEARCH([SelectedCategory],Data[Data]), -1) > -1 
    		)  
    	)


    The output of this measure will be the associated number on the Data table based on a text search using the selected slicer value. 

     

     




    Javier Guillen
    http://javierguillen.wordpress.com/
    • Proposed as answer by Challen Fu Thursday, September 15, 2011 6:48 AM
    • Marked as answer by Challen Fu Thursday, September 22, 2011 9:57 AM
    Wednesday, September 14, 2011 6:34 PM
    Answerer
  • That was great.... there gullien.... Thanks... but i wanted a slight modified solution like its summing up the selected values (Banana) but i wanted to display as the data is.

    To be clear, I select banana and i want all the records as it is there the the data table (both columns....). Thanks in advance

    Thursday, September 15, 2011 6:00 AM