topic Re: Only show rows with a specific character in a matrix table (measure) in DAX Commands and Tips

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

Anonymous — Wed, 21 Feb 2024 09:47:19 GMT

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

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

talespin — Wed, 21 Feb 2024 09:55:31 GMT

hi Anonymous

Please share sample data(Source data).

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

Anonymous — Wed, 21 Feb 2024 10:27:04 GMT

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?

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

Anonymous — Wed, 21 Feb 2024 10:27:42 GMT

@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?

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

talespin — Wed, 21 Feb 2024 10:42:00 GMT

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.

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

Anonymous — Wed, 21 Feb 2024 12:42:29 GMT

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!

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

talespin — Wed, 21 Feb 2024 15:46:02 GMT

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() )

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

Anonymous — Wed, 21 Feb 2024 16:05:54 GMT

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.

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

talespin — Thu, 22 Feb 2024 02:52:29 GMT

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.