Text Slicer for multiple Rows

[Phonetic]

Hi everyone,

I'll first try to explain how my data is structured. I have a table in which each row represents a project. People from different departments are assigned to this project.

There is a column for each department, which can either be empty or contain one or more abbreviations of names.

Projectname	Sales	Quality	Projectmanagement	Logistic
Project X	MM		FF	OO
Project Y	PP	II	FF	CC
Project Z	ZZ	RR	WW

There is also a table that assigns the name abbreviations to the correct person (MM ; Max Mustermann)

abbreviation	Full name	departments
MM	Max Mustermann	Sales
FF	French Fries	Projectmanagement
CC	Clumy Clown	Logistic
...	...	...

I would now like to create a slicer that searches through all columns based on an entered abbreviation and shows me each project where this abbreviation appears in one of the columns. So I want to see which projects, for example, MM is assigned to.

Thank you! 🙂 

[Phonetic]

and depivotisation would not be the best solution either, as there is a lot more data in the table and i would like to retain the structure. But thank you!!

[wini_R · Accepted solution]

PBI works best with tabular format and others might not be very efficient.
If you need a filter for your structure anyway, you can try this approach:
(1) add a measure that will be used as visual level filter

ProjectFilter = 
VAR _selAbbrev = SELECTEDVALUE(tbl2a[abbreviation], 0)
VAR _sales = SELECTEDVALUE(tbl2[Sales])
VAR _logistic = SELECTEDVALUE(tbl2[Logistic])
VAR _pm = SELECTEDVALUE(tbl2[Projectmanagement])
VAR _quality = SELECTEDVALUE(tbl2[Quality])
RETURN
IF(
    ISFILTERED(tbl2a[Full name]), 
    IF(_sales = _selAbbrev || _logistic = _selAbbrev || _pm = _selAbbrev || _quality = _selAbbrev, 1, 0),
    1
)

(2) add it to visual that you want to be filtered, ie. table

(3) outcome

[Phonetic]

Thank you so much! This works perfectly 🙂 

[wini_R]

Hey @Phonetic,

The easierst way to solve this would be:
(1) unpivoting your top table to this format:

(2) creating a relationship between the tables

(3) adding a table (or other visual) and a slicer to the report

[Phonetic]

I have also thought about connecting the two tables, but I can only create relations between two columns and not one column to several columns in the other table, right?

[wini_R]

After unpivoting your table those multiple columns would become just one column - see Depart. column in the first screen shot. Then you can easily use it to connect tables