Filtering matrix rows based on multiples columns

[Anonymous]

Hello,

Can anyone help me with a solution to this?
We have built a Matrix report but need to filter rows based on the value of at least two column in a matrix.

Vendor	Corporate	Energy	Defence
Supplier 1	Z04M	Z06M
Supplier 2	Z06M
Supplier 3		Z06M	Z06M
Supplier 4	Z09M
Supplier 5	Z09M	Z03M

We want to keep only Supplier 1, 3 & 5.

(and the dream could be only keep Supplier 1 & 5 because value are not the same)

Thanks for your help,
Julian.

[amitchandak]

Filter on Energy <> Blank (Visual level)

Or have a measure like

Calculate(Sum(Table[Value]),not(isblank(Table[Energy])))

[parry2k · Accepted solution]

@Anonymous First your table needs to be transformed for better design, basically unpivot your table, to unpivot follow these steps:

- transform data
- select vendor column
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement

- filter blank values rows (we don't need these)
- close and apply

To visualize,
- matrix visual:
- add vendor on rows,
- add attribute on columns
- add value on values section

Add the following measure:

Count Measure = CALCULATE ( DISTINCTCOUNT ( Vendor[Value] ), ALLEXCEPT ( Vendor, Vendor[Vendor] ) )

and use this measure as visual level filter on above matrix visual

and here is the result

Here is  M script to unpivot, create a blank query and in advanced editor paste the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4tKMjJTC1SMFTSUYoyMPEFU2YgSkEpVgdJgRGyDIasMVQYqgJMoSgwAYta4tBuipCNMjAGUUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Vendor = _t, Corporate = _t, Energy = _t, Defence = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"Corporate", type text}, {"Energy", type text}, {"Defence", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Vendor"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "" and [Value] <> " "))
in
    #"Filtered Rows"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Thanks lot @parry2k , it's perfect 🤗.
(Apologies, my initial source was not build as i explain, i appreciate your recommandation).

[Anonymous]

Hello @amitchandak,
the filter is not based on "Energy" column but for rows with a value in at least 2 columns.