Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ferk_
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: 

ferk__0-1708508727525.png


To this:

ferk__1-1708508778613.png

 

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


1 ACCEPTED SOLUTION

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.

talespin_0-1708530232016.png

 

 

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

 

talespin_2-1708530347458.png

 

 

View solution in original post

8 REPLIES 8
talespin
Solution Sage
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.

EmployeeTraining 1Training 2Training 3Training 4Training 5Training 6
Employee 1X   
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!

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.

talespin_0-1708530232016.png

 

 

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

 

talespin_2-1708530347458.png

 

 

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. 

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.

talespin_0-1708570309410.png

 

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 this can be solved?

talespin
Solution Sage
Solution Sage

hi @ferk_ 

 

Please share sample data(Source data).

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors