cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MKealey Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
MKealey Frequent Visitor
Frequent Visitor

Re: Slicer Hierarchy Search

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:

Capture.PNG

 

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":

 Capture2.PNG

 

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:

Capture3.PNG

 

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:

 

 Capture4.PNG

 

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: Slicer Hierarchy Search

Hi @MKealey ,

 

Suppose sample table is like this.

6.PNG

 

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

2.PNG3.PNG

 

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

4.PNG5.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
MKealey Frequent Visitor
Frequent Visitor

Re: Slicer Hierarchy Search

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:

Capture.PNG

 

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":

 Capture2.PNG

 

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:

Capture3.PNG

 

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:

 

 Capture4.PNG

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 243 members 2,870 guests
Please welcome our newest community members: