Forum Discussion

DianaT's avatar
DianaT
Icon for Helper I rankHelper I
6 years ago
Solved

Filter

Hi all,

 

I have a table with sales manager details recorded as below:

 

Abbey

AbbeyHazel

Peter

PeterAbbey

Hazel

PeterHazel

 

Is there any way I can create a filter which will allow me to filter all rows that contain the name "Abbey", "Hazel" and "Peter"?

 

Many thanks,

Diana

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try to duplicate the original column and use 'Split Columns' function to split duplicated column in Query Editor:

    After Apply&Close, create a Slicer table:

    Table 2 = DISTINCT(UNION(SELECTCOLUMNS('Table',"A",'Table'[Sales Manage - Copy.1]),SELECTCOLUMNS('Table',"B",'Table'[Sales Manage - Copy.2])))

    Then create a check measure:

    Measure = IF(SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.1])||SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.2]),1,0)

    Apply this measure=1 to the visual, when you choose a name in slicer, the result shows:

    Here is my test pbix file:

    https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-gizhi_microsoft_com/EaRtvIw6e5tMnwQaqwfraOIBaxH7z7CvTaFsqucUggp9jA?e=A9G0cP 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Are you going to select these values in a slicer?

     

    If yes, then refer to this example. Not the same but when each value is selected you can use search and find and build the final result.

     

    https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Connect on Linkedin

     

     

    • DianaT's avatar
      DianaT
      Icon for Helper I rankHelper I
      6 years ago

      Thanks for your reply.

       

      I'm still wrapping my head around on how I could apply this approach to my data and make it work... (Being a newbie certainly doesn't help...)

       

      I'll let you if I make any progress on this 🙂

       

      Diana

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi,

     

    Please try to duplicate the original column and use 'Split Columns' function to split duplicated column in Query Editor:

    After Apply&Close, create a Slicer table:

    Table 2 = DISTINCT(UNION(SELECTCOLUMNS('Table',"A",'Table'[Sales Manage - Copy.1]),SELECTCOLUMNS('Table',"B",'Table'[Sales Manage - Copy.2])))

    Then create a check measure:

    Measure = IF(SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.1])||SELECTEDVALUE('Table 2'[A]) in FILTERS('Table'[Sales Manage - Copy.2]),1,0)

    Apply this measure=1 to the visual, when you choose a name in slicer, the result shows:

    Here is my test pbix file:

    https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-gizhi_microsoft_com/EaRtvIw6e5tMnwQaqwfraOIBaxH7z7CvTaFsqucUggp9jA?e=A9G0cP 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

    • DianaT's avatar
      DianaT
      Icon for Helper I rankHelper I
      6 years ago

      Hi Giotto,

       

      I like this... Way leaner than the solution I have (which involves uploading seperate datalists for each manager...)

       

      My next obstacle though is that I'm trying to have my visual looks like this:

       

      But for this filter to work, I have to include manager info in my table:

       

      When I take away the Manager columns, the filter stops working and pulling in data from everyone...

       

       

      I would very much appreciate your insight on this.

       

      Many thanks,
      Diana