How to filter matrix based on column data

[mijoe]

I have a set of data like this:

	BaseColumn	ColB	ColC
value 1	y	n	n
value 2	y	y	y

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? 

[v-jingzhang]

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)

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]

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. 

[mijoe]

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?

Name	ColType	Value
value1	BaseColumn	y
value1	ColB	n
value1	ColC	y
value2	BaseColumn	y
value2	ColB	y
value2	ColC	y

[Vijay_A_Verma]

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"