Only show rows with a specific character in a matrix table (measure)

[Anonymous]

Hi,

I have a matrix table with several columns. I need a measure that filters to only show the rows with a specific character, "x" in this case, in the values like this: 

To this:

Take into account that I have 20 values for each Row, so I need a way to do this automatically in case I add another value. 

Any help/tip is welcome. If you need more information/clarification tell me.

Thank you,
Ferk

[talespin]

hi @Anonymous 

You can type in data manually, made up number(Remove any sensitive infromation). I Just need to understand what your source table looks like in order to answer your question.

[Anonymous]

Employee	Training 1	Training 2	Training 3	Training 4	Training 5	Training 6
Employee 1	X	✓	✓	✓
Employee 2		X	✓	✓	✓
Employee 3		✓	✓	✓		✓
Employee 4	✓	✓	✓
Employee 5			X	X	✓	✓

Looks like this:
So after the filter in the matrix table I want to only be able to see the trainings not completed (X) from employees 1, 2 and 5. I hope this sample is enough for you to understand. Thank you, once again!

[talespin · Accepted solution]

hi @Anonymous 

It requires a bit of Power Query as well.

Step1 : In power Query select Employee column and Unpivot other columns. Make sure you do not reference Training columns anywhere.

Step2 :

Create a measure and apply as a filter on Matrix.

MFilter =

VAR _checkx = CALCULATE( VALUES(Training[Value]), REMOVEFILTERS(Training[Attribute]), Training[Value] = "x")

RETURN IF(_checkx = "x", 1, BLANK() )

[Anonymous]

What a really good approach! But you know if it is also possible to have only the trainings that have "x", so in that case Training 5 and 6 would be deleted. 

[talespin]

hi @Anonymous 

Thats even simpler. you don't need any measure.

Step1 : In power Query select Employee column and Unpivot other columns. Make sure you do not reference Training columns anywhere.

Step2 Apply filter on value column for this visual.

[Anonymous]

@talespin Thank for your answer! I'm in a work environment, I can't use those websites to upload a file and share a link. But you have any ideia on how this can be solved?

[talespin]

hi @Anonymous 

Please share sample data(Source data).

[Anonymous]

@talespin Thank for your answer! I'm in a work environment, I can't use those websites to upload a file and share a link. But you have any ideia on how my problem can be solved?