topic Re: Suppressing two columns from one columns values. in DAX Commands and Tips

Suppressing two columns from one columns values.

Marvhall — Mon, 18 Sep 2023 18:55:56 GMT

I have a matrix table set up with IF statements to identify suppression rules. What I need is both columns to be suppressed when column A is supressed. Below is screenshot of what I have vs what I need.

Any ideas?

What I have
Data Column A	For	Against
A	97	1,750
B	385	32,203
C	**	1,234
D	45	87
E	**	7,024


What I need
Data Column A	For	Against
A	97	1,750
B	385	32,203
C	**	**
D	45	87
E	**	**

Thanks!

Re: Suppressing two columns from one columns values.

Anonymous — Wed, 20 Sep 2023 01:44:43 GMT

Hi Marvhall ,

Please have a try.

Create a meausre.

Measure = IF(MAX('Table'[For])="**","**",MAX('Table'[Against]))

Or a column.

Column = VAR _1 = CONVERT ( 'Table'[Against], STRING ) RETURN IF ( 'Table'[For] = "**", "**", _1 )

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Suppressing two columns from one columns values.

lbendlin — Wed, 20 Sep 2023 02:03:12 GMT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUbI0BxKGOuamBkqxOtFKTkCesYUpiDTSMTIwBgs6A7laWmB1RsYmYCEXIM8EpMzCHMx3hSkx1zEwAiqJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data Column A" = _t, For = _t, Against = _t]), #"Replaced Value" = Table.ReplaceValue(Source,each [Against],each if [For]="**" then [For] else [Against],Replacer.ReplaceValue,{"Against"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Against", type text}}) in #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Re: Suppressing two columns from one columns values.

Marvhall — Wed, 20 Sep 2023 12:58:20 GMT

Anonymous

This works when the values are in their own column. See below for column make up.

What I am doing to get your solution is to reference the main table and select these columns, unvpivot Group A, then Pivot on Group A to get them into their own column headings.

Thoughts? or do you have a better solution?