topic Measure to check one column for a condition, then sum the rest of another column in DAX Commands and Tips

Measure to check one column for a condition, then sum the rest of another column

redalert787 — Fri, 27 Jan 2023 10:49:17 GMT

Hello,

I am trying to build a DAX equation to help show some data in a way the database is not set up.

Here is the context, I have 2 columns: 1)Old/New 2)Special/Range

What I would like to do is create a dax condition that shows me all the OLD then of the new how much is special/range.

What my database currently looks like is something like this:

Ideally what I want to do is create it so that it looks like the below to create

To create the charts that look like this :

Re: Measure to check one column for a condition, then sum the rest of another column

johnt75 — Fri, 27 Jan 2023 12:23:50 GMT

You could create a new conditional column in Power Query. If the old/new column equals old then use that column else use the special/range column. You can then include this column in your visuals with a measure summing sales.

Re: Measure to check one column for a condition, then sum the rest of another column

v-yueyunzh-msft — Mon, 30 Jan 2023 03:34:47 GMT

Hi , redalert787

According to your description, you want to convert the table , for your need , it can be realized easiler in Power Query Editor.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can put this M code in the Advanced Editor" in Power Query Editor:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys9JUdJRCi5ITc5MzAGyDA0MlGJ1YOJFiXnpqUiifqnlJIpimoxVPBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"OLD/new" = _t, #"Special/range" = _t, Sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OLD/new", type text}, {"Special/range", type text}, {"Sales", Int64.Type}}), #"New Table" = Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(#"Changed Type", each ([#"OLD/new"] = "New")),{"OLD/new"}),{{"Special/range", "Column"}}), #"Old Table" = Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(#"Changed Type", each ([#"OLD/new"] = "old")),{"Special/range"}),{{"OLD/new", "Column"}}), Custom1 = #"Old Table" & #"New Table" in Custom1

(3)Then we can get the table you need:

(4)Then we can apply the data to Desktop, and we put the fields on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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