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

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

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
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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