topic DAX: Delete empty columns after filtering in DAX Commands and Tips

DAX: Delete empty columns after filtering

Merleau — Sun, 03 May 2020 22:16:32 GMT

Hello,

I have a table in my report similar to this:

ID	Zipcode	Red	Blue	Pink	Magneta
Id1	20T00		Blue
Id2	20T00	Red		Pink
Id3	50R30			Pink	Magneta
Id4	50Q50	Red
Id5	50Q50		Blue

After applying a filter on “Zipcode”,

Filter (Zipcode) = 50R30

I would like to have my table displayed as below without empty columns (Red and Blue):

ID	Zipcode	Pink	Magneta
Id3	50R30	Pink	Magneta

Could you pls help?

Thank you.

Re: DAX: Delete empty columns after filtering

VasTg — Sun, 03 May 2020 22:56:20 GMT

Merleau

You have to modify your input table to get what you want.

Input:

You should transform the input table into this.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjIIMTAA0kDklFOaCmEpxeqA5I2Q5INSUyByAZl52QglxkCmqUGQMdQIhLxvYnpeakkiVJkJWFmgMYpJCFNMkaTRHBILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Zip = _t, Red = _t, Blue = _t, Pink = _t, Magneta = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Zip", type text}, {"Red", type text}, {"Blue", type text}, {"Pink", type text}, {"Magneta", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Zip"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Color"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> "")) in #"Filtered Rows"

Follow the Unpivot and filtered steps.

Then create a matrix with stepped layout off as below

When you filter the zip, you get the expected output.

The idea is to keep the color columns as values to not show the column altogether.

If it helps, mark it as a solution

Kudos are nice too

Re: DAX: Delete empty columns after filtering

Merleau — Thu, 14 May 2020 20:41:58 GMT

Thank you VasTg

This appears to solve my problem.

Althought it requires me to change my input in Power Query first, this is great.

Sorry for my late response.

I appreciate the detailed response.

Is there any way to achieve this using "Table"?

Matrix works perfectly but the end result will be exported to Excel for further analysis and formatting. Unfortunately Power Bi does not support exporting matrix "as it is" at the moment. ... Therefore, I am still looking for a way around because we need to filter the matrix for the all the 25 values in zipcode and export to Excel.

Thanks again.