Forum Discussion
Multiple Column Filter
Hello
I am attempting to create a filter based on the below table. The goal would be create a filter with all the distinct values in the column Manager1 and use that as a filter for all four columns.
For Example:
The filter for the below would contain the values ManagerA, ManagerB, ManagerC, ManagerD, ManagerE because they are the distinct values in the Manager1 column.
So if you filtered on ManagerC, you could see Employee A, Employee B, Employee D, Employee F, regardless of what column they are in.
| Manager1 | Manager2 | Manager3 | Manager4 | |
| Employee A | ManagerA | ManagerB | ManagerC | ManagerD |
| Employee B | ManagerC | ManagerD | ||
| Employee C | ManagerD | |||
| Employee D | ManagerB | ManagerC | ManagerD | |
| Employee E | ManagerE | ManagerF | ||
| Employee F | ManagerB | ManagerC | ManagerD |
Let me know if you have any questions. Thank you in advance for the help.
Andrew
Hi, arosenberg
Thank you for your swift response. Based on the data structure you’ve provided, I have two proposals:
Before we begin with either option, we need to prepare the slicer. Therefore, I have created the following calculation table to generate columns for all managers, which will serve as the slicer.
Table 2 = VAR n1=SELECTCOLUMNS('Table',"1",'Table'[Manager1]) VAR n2=SELECTCOLUMNS('Table',"1",'Table'[Manager2]) VAR n3=SELECTCOLUMNS('Table',"1",'Table'[Manager3]) VAR n4=SELECTCOLUMNS('Table',"1",'Table'[Manager4]) RETURN DISTINCT(UNION(n1,n2,n3,n4))
Of course, this step's calculation table can also be processed using Power Query. Below are the detailed M language instructions:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1VcFTSUTI0AhLOOZmpeSVgfkBRfkppMoRtAsQWQOybmJeYnlrkiGA6IZjOCKaLUqwOHuOdkIx3otx4kCJjE+zGO0KNtsBhkI6SAhijGAhSYWqGMNAZzb1GeqZA0hxMYphE0DQ3JNNA2owpMg05qkByhiSZBpIyt0CY5oJmGlrQ4YoODHNdgUKWBghzXdH8jGauK4Lpht2hIGFDQ+yB6ESiQ2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [em = _t, #"em #" = _t, Client = _t, Product = _t, Hours = _t, #"Sum Hours" = _t, Manager1 = _t, Manager2 = _t, Manager3 = _t, Manager4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"em", type text}, {"em #", Int64.Type}, {"Client", type text}, {"Product", type text}, {"Hours", type number}, {"Sum Hours", type number}, {"Manager1", type text}, {"Manager2", type text}, {"Manager3", type text}, {"Manager4", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"em #", "Client", "Product", "Hours", "Sum Hours"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"em"}, "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"em", "Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns1") in #"Removed Duplicates"1.For the first proposal:
Firstly, you will need to select these fields in Power Query and then merge them.
Secondly, please use the following measures and apply them to the filters of the visual objects:
MEASURE = IF ( ISFILTERED ( 'Table 2'[1] ), IF ( CONTAINSSTRING ( MAX ( 'Table'[Merged] ), MAX ( 'Table 2'[1] ) ), 1, 0 ), 1 )Here are the final results:
2.For the second proposal:
You can directly use the following measures and apply them to the filters of the visual objects:
Measure2 = VAR cc1 = MAX ( 'Table 2'[1] ) RETURN IF ( ISFILTERED ( 'Table 2'[1] ), IF ( CONTAINS ( 'Table', 'Table'[Manager1], cc1 ) || CONTAINS ( 'Table', 'Table'[Manager2], cc1 ) || CONTAINS ( 'Table', 'Table'[Manager3], cc1 ) || CONTAINS ( 'Table', 'Table'[Manager4], cc1 ), 1, 0 ), 1 )Here are the final results:
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Have a look at https://www.daxpatterns.com/parent-child-hierarchies/
hey hi ,see teh below sb ,
reate a Table for All Distinct Managers:
You want to create a distinct list of managers from all four manager columns (Manager1, Manager2, Manager3, Manager4). We can use DAX to unpivot these columns into a new table, which will give us a list of all managers.
Here's how you can do this in Power Query:In Power Query (Transform Data), select your table.
Select the four manager columns (Manager1, Manager2, Manager3, Manager4).
Right-click and choose Unpivot Columns. This will create two new columns:
Attribute (containing Manager1, Manager2, etc.),
Manager (containing all distinct managers from the four columns).
The result will be a new table with all managers in a single column:Employee Manager
Employee A ManagerA
Employee A ManagerB
Employee A ManagerC
Employee A ManagerD
Employee B ManagerC
Employee B ManagerD
Employee C ManagerD
Employee D ManagerB
Employee D ManagerC
Employee D ManagerD
Employee E ManagerE
Employee E ManagerF
Employee F ManagerB
Employee F ManagerC
Employee F ManagerD
This approach ensures that the manager names are now in a single column, which can be used to filter employees.
Create a Slicer:Now that you have a column (Manager) with all distinct manager names, you can add this column to a slicer in your report.
In your slicer, select the Manager column from the unpivoted table. This slicer will allow you to select a specific manager.Thanks for the reply from Laxmanjatoth and johnt75 please allow me to provide another insight:
Hi, arosenberg
My approach is almost identical to that of Laxmanjatoth . I’ve created the following example to aid your understanding:
1.Firstly, let’s assume your original data looks like this:
2.Secondly, in Power Query, select all columns except the first one, as shown in the image below:
3.Then, remove any unnecessary columns:
4.Next, create the visualisation objects and slicers in the Desktop application:
You may need to note that there are null values in the slicer options, so we should add an additional filter:
5.Here's my final result, which I hope meets your requirements.
Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
When uploading a file, please be careful to delete sensitive information.
For questions about uploading data, you can try the following links:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Solved: How to upload PBI in Community - Microsoft Fabric Community
When uploading a file, please be careful to delete sensitive information.
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Just to complicate it a bit more, what if there are multiple other columns? Not just manager and employee. Similar to the below. Will these solutions still work? I tried a few and haven't been able to figure out.
em em # Client Product Hours Sum Hours Manager1 Manager2 Manager3 Manager4 Employee A 12 Client A Product A 4 8 ManagerA ManagerB ManagerC ManagerD Employee A 12 Client B Product B 4 8 ManagerA ManagerB ManagerC ManagerD Employee B 34 Client B Product A 8 8 ManagerC ManagerD Employee C 56 Client C Product B 2.5 7.5 ManagerD Employee C 56 Client F Product D 3.5 7.5 ManagerD Employee C 56 Client A Product C 1.5 7.5 ManagerD Employee D 78 Client D Product C 8 8 ManagerB ManagerC ManagerD Employee E 90 Client E Product D 8 8 ManagerE ManagerF Employee F 11 Client F Product B 8 8 ManagerB ManagerC ManagerD Is there a way to just see records from employees who report to ManagerC?
Thanks everyone for the responses
Hi, arosenberg
Thank you for your swift response. Based on the data structure you’ve provided, I have two proposals:
Before we begin with either option, we need to prepare the slicer. Therefore, I have created the following calculation table to generate columns for all managers, which will serve as the slicer.
Table 2 = VAR n1=SELECTCOLUMNS('Table',"1",'Table'[Manager1]) VAR n2=SELECTCOLUMNS('Table',"1",'Table'[Manager2]) VAR n3=SELECTCOLUMNS('Table',"1",'Table'[Manager3]) VAR n4=SELECTCOLUMNS('Table',"1",'Table'[Manager4]) RETURN DISTINCT(UNION(n1,n2,n3,n4))
Of course, this step's calculation table can also be processed using Power Query. Below are the detailed M language instructions:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1VcFTSUTI0AhLOOZmpeSVgfkBRfkppMoRtAsQWQOybmJeYnlrkiGA6IZjOCKaLUqwOHuOdkIx3otx4kCJjE+zGO0KNtsBhkI6SAhijGAhSYWqGMNAZzb1GeqZA0hxMYphE0DQ3JNNA2owpMg05qkByhiSZBpIyt0CY5oJmGlrQ4YoODHNdgUKWBghzXdH8jGauK4Lpht2hIGFDQ+yB6ESiQ2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [em = _t, #"em #" = _t, Client = _t, Product = _t, Hours = _t, #"Sum Hours" = _t, Manager1 = _t, Manager2 = _t, Manager3 = _t, Manager4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"em", type text}, {"em #", Int64.Type}, {"Client", type text}, {"Product", type text}, {"Hours", type number}, {"Sum Hours", type number}, {"Manager1", type text}, {"Manager2", type text}, {"Manager3", type text}, {"Manager4", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"em #", "Client", "Product", "Hours", "Sum Hours"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"em"}, "Attribute", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"em", "Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns1") in #"Removed Duplicates"1.For the first proposal:
Firstly, you will need to select these fields in Power Query and then merge them.
Secondly, please use the following measures and apply them to the filters of the visual objects:
MEASURE = IF ( ISFILTERED ( 'Table 2'[1] ), IF ( CONTAINSSTRING ( MAX ( 'Table'[Merged] ), MAX ( 'Table 2'[1] ) ), 1, 0 ), 1 )Here are the final results:
2.For the second proposal:
You can directly use the following measures and apply them to the filters of the visual objects:
Measure2 = VAR cc1 = MAX ( 'Table 2'[1] ) RETURN IF ( ISFILTERED ( 'Table 2'[1] ), IF ( CONTAINS ( 'Table', 'Table'[Manager1], cc1 ) || CONTAINS ( 'Table', 'Table'[Manager2], cc1 ) || CONTAINS ( 'Table', 'Table'[Manager3], cc1 ) || CONTAINS ( 'Table', 'Table'[Manager4], cc1 ), 1, 0 ), 1 )Here are the final results:
Please find the attached pbix relevant to the case.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, Thank you so much for this solution.
This worked well. One question for you, is there a way to make it so you can filter on multiple managers at a time?
