Forum Discussion

mijoe's avatar
mijoe
Microsoft Employee
4 years ago

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? 

Need Help

4 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
      mijoe
      Microsoft Employee
      4 years ago

      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
      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        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"

         

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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.