Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Slicer Hierarchy Search

Hi,

 

I was wondering if it is possible to search two different columns in a table using something similar to a Heirarchy slicer.

 

My problem is:

 

I have two different columns: "Project Head" and "Project Lead". I would like to search for people in one of these columns, or both using a heirarchy slicer. The Slicer would look something like this:

 

  • Bob
    • Project Head
    • Project Lead
  • John
    • Project Head

...

 

I would like to be able to filter for Bob, searching both columns if I select both "Project Head" and "Project Lead".

Performing a different search, I would like to be able to filter the table for Bob in only "Project Head"

 

Let me know if this is possible. Thanks in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks for you're response! This was not exactly what I was looking for but pushed me in the right direction.

     

    First I took my original table and added an index column to it:

     

    Then, I made a copy of this table, deleting the "Project Name" column as this was not needed. I then used "Unpivot Columns" on columns "Project Lead" and "Project Head":

     

     

    This then allowed me to close my queries, and edit the relationships in the main page. For this, I made a relationship between the indexes, Cross Filtering in Both Directions:

     

    I could then create my table and Hierarchy Slicer. The Hierarchy Slicer can filter the table for one column, or both, depending on the selection:

     

     

     

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous ,

     

    Suppose sample table is like this.

     

    In Query editor mode, choose [Project Head] and [Project Lead], click "unpivot columns" to convert table to below structure.

     

    In report view mode, drag [Name] and [Head/Lead] fields into custom visual HierarchySlicer. And use a Matrix visual to display data.

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for you're response! This was not exactly what I was looking for but pushed me in the right direction.

       

      First I took my original table and added an index column to it:

       

      Then, I made a copy of this table, deleting the "Project Name" column as this was not needed. I then used "Unpivot Columns" on columns "Project Lead" and "Project Head":

       

       

      This then allowed me to close my queries, and edit the relationships in the main page. For this, I made a relationship between the indexes, Cross Filtering in Both Directions:

       

      I could then create my table and Hierarchy Slicer. The Hierarchy Slicer can filter the table for one column, or both, depending on the selection: