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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mijoe
Microsoft Employee
Microsoft Employee

How to filter matrix based on column data

I have a set of data like this:

 

 BaseColumnColBColC
value 1ynn
value 2yyy

 

I want to filter to only value1 because BaseColumn=y while the other columns=n.  The condition I'm looking for is BaseColumn=y && if any other column=n, then show it.  Does anyone have any suggestions for how to easily do this? 

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @mijoe 

 

@Vijay_A_Verma 's solution will transform the underlying data table and filter out rows that don't meet the condition. Have you tried that?

 

If you don't want to transform the underlying table and only want to hide the rows from the matrix visual at the report layer, you can try my method as below. 

 

Create the following measure and put it to the filter pane on the matrix visual. Set its value to 1. 

Flag = 
VAR _baseColumnValue = CALCULATE(SELECTEDVALUE('Data'[Value]),ALL(Data[ColType]),Data[ColType]="BaseColumn")
VAR _allNoCount = CALCULATE(COUNT(Data[Value]),ALL(Data[ColType]),Data[ColType]<>"BaseColumn",Data[Value]="n")
VAR _allCount = CALCULATE(COUNT(Data[Value]),ALL(Data[ColType]),Data[ColType]<>"BaseColumn")
RETURN
IF(_baseColumnValue = "y" && _allNoCount = _allCount,1,0)

vjingzhang_0-1647591502881.png

 

I have attached the pbix at bottom. Let me know if you have any questions. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Vijay_A_Verma
Super User
Super User

Assuming your columns are fixed in number, the easiest way is to put following formula in a Custom column

if [BaseColumn]="y" and ([ColB]="n" or [ColC]="n") then "Yes" else "No"

Then filter on Yes on this Custom column and delete custom column after the filter. 

Thanks for the reply.  I'm not sure if this works based on the way the table is setup.  Would it work if the he underlying data looks something like the table below?

 

NameColTypeValue
value1BaseColumny
value1ColBn
value1ColCy
value2BaseColumny
value2ColBy
value2ColCy

Solution Excel workbook uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuST6bYrK9x8Xm5kO?e=GH4UsG 

For scenario 2, use following code

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[ColType]), "ColType", "Value"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each if [BaseColumn]="y" and ([ColB]="n" or [ColC]="n") then "Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors