cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

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

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.

Thank you,
Ferk

1 ACCEPTED SOLUTION
Solution Sage

hi @ferk_

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

8 REPLIES 8
Solution Sage

hi @ferk_

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.

Frequent Visitor
 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!

Solution Sage

hi @ferk_

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

Frequent Visitor

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.

Solution Sage

hi @ferk_

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.

Frequent Visitor

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

Solution Sage

hi @ferk_

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors