How to show all filled columns in a matrix which contain sliced value

[7otto8]

Hello,

I'm looking for a way to show all filled columns in a matrix after using a slicer to filter data down to a select few.

So I've created a fictive matrix with Company names as rows, employee names as values & Employee number as column.

My fictive matrix looks like this;

Company_name | Employee 1 | Employee 2 | Employee 3 | Employee 4 etc.

Company1          | Truus           | Henk            | Kees

Company2          | Kees            | Albert

Company3          | Hanz           | Eric               | Thomas       | Eduardo

Afterwards I've added a slicer for employee names in the hope to get the company + all employee names who work at the same company as Henk.

The result I get when I slice for Henk =

Company_name  | Employee 2 |

Company1          | Henk            | 

The result I wanted when I filter for Henk = 

Company_name | Employee 1 | Employee 2 | Employee 3 | Employee 4 etc.

Company1          | Truus           | Henk            | Kees

And if I were to filter Kees I'd wanted = 

Company_name | Employee 1 | Employee 2 | Employee 3 | Employee 4 etc.

Company1          | Truus           | Henk            | Kees

Company2          | Kees            | Albert

Is this possible to achieve?

[Anonymous · Accepted solution]

Hi  @7otto8 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Value])

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Table 2'[Value])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Value]=_select),"1",[Company_name])
return
IF(
    MAX('Table'[Company_name])  in _column,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly