cancel
Showing results for
Did you mean:
Highlighted
Resolver I

## Create new column based conditions of other two columns

 Date Column A Column B Column D - new column 1/2/2019 100 1% 100 1/3/2019 200 0% 0 1/4/2019 100 0% 0 1/5/2019 300 2% 600 1/6/2019 100 .1% 100 1/7/2019 50 0% 0 1/8/2019 50 0% 0 1/9/2019 100 0% 0 1/10/2019 200 3% 400

The above table show Date, column A and colum B as current fields. Column C would be the DAX calculated column. So, the logic is that if column B has <>0 value that it will take the value in column A and store it in column C. If value in column B is 0 then it will store a 0 value in column C. However, it should sum up all values in column A and store and add it to value in Column A where column B is <> 0.  for example from 1/7/2019 to 1/9/2019 column C is showing 0. However, on 1/10/2019 column C is showing 400. That is because it is summing values in Column A from 1/7/2019 to 1/9/2019 and adding it to value on 1/10/2019 and showing the result in column C.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III

## Re: Create new column based conditions of other two columns

The following should work @rschaudhr

``````Column C =
VAR varCurrentDate = 'Table'[Date]
VAR varStartDate =
MAXX(
FILTER(
'Table',
'Table'[Date] < varCurrentDate
&& 'Table'[Column B] <> 0
),
'Table'[Date]
)
VAR varTotal =
SUMX(
FILTER(
'Table',
'Table'[Date] > varStartDate
&& 'Table'[Date] <= varCurrentDate
),
'Table'[Column A]
)
VAR Result =
IF(
'Table'[Column B] <> 0,
varTotal,
0
)
RETURN
Result
``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Resolver I

## Re: Create new column based conditions of other two columns

Hi edhans,

This solution worked very well! Thank you!

I have another question. If I want to incorporate this same logic in Transform Data section, how would I do that.

thanks.

Highlighted
Super User III

## Re: Create new column based conditions of other two columns

You mean in Power Query? Just so you know, I am heavily heavily biased in providing answers in Power Query vs Calculated columns (links below) unless it doesn't make sense. And in this case, I do not think it does. This is an analysis that, IMHO, is best done in DAX using a calculated column or a measure. Power Query doesn't do well when scanning large tables "filtering" on the fly like this. For 10,000 rows, it doesn't matter. For 1,000,000 rows, Power Query may never finish. I am trying to think of a way using grouping or row selection that would avoid this but am coming up blank.

While I am not a fan of calculated columns, this is one of those instances where I think it will perform better than Power Query - unless someone with more skill than I (or imaginiation) can come up with a way. That said, this will work, but I will not swear it will perform well on large data sets. Test it and let me know. If you like it, it is preferable to Calculated Columns, all other things being equal.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMLRU0lEyNDAAkapKsTogcWOYuBFY3AAmboKqHi5uChM3BosbwcTN0NTrGcK1mMOkTFFMssAubInDYkMDVJcaAyViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Column A" = _t, #"Column B" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column A", Int64.Type}, {"Column B", Percentage.Type}}),
RowData =
#"Changed Type",
"Current Data",
each
let
varCurrentDate = [Date],
varPreviousDate =
List.Max(
Table.SelectRows(
#"Changed Type",
each ([Date] < varCurrentDate and [Column B] <> 0)
)[Date]
)
in
if [Column B] = 0 then 0
else
List.Sum(
Table.SelectRows(
#"Changed Type",
each ([Date] = varCurrentDate) or ([Date] < varCurrentDate and [Date] > varPreviousDate and [Column B] = 0)
)[Column A]
),
Int64.Type
)
in
RowData``````

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

Please mark one or both as solutions, give kudos to thing that help, etc. 😁
I am most interested on the Power Query code on your full data set and how it performs.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
9 REPLIES 9
Highlighted
Continued Contributor

## Re: Create new column based conditions of other two columns

What is the objective of your task, do you want to show those cumulative numbers by date in the Table visual? If so then cumulatative dax will work with criteria. Let me know why do you want calculate column in your table and not measure?

Highlighted
Resolver I

## Re: Create new column based conditions of other two columns

Yes, it is cummulative based on Date. However, the condition are set based on column B.

Highlighted
Super User III

## Re: Create new column based conditions of other two columns

The following should work @rschaudhr

``````Column C =
VAR varCurrentDate = 'Table'[Date]
VAR varStartDate =
MAXX(
FILTER(
'Table',
'Table'[Date] < varCurrentDate
&& 'Table'[Column B] <> 0
),
'Table'[Date]
)
VAR varTotal =
SUMX(
FILTER(
'Table',
'Table'[Date] > varStartDate
&& 'Table'[Date] <= varCurrentDate
),
'Table'[Column A]
)
VAR Result =
IF(
'Table'[Column B] <> 0,
varTotal,
0
)
RETURN
Result
``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Resolver I

## Re: Create new column based conditions of other two columns

Hi edhans,

This solution worked very well! Thank you!

I have another question. If I want to incorporate this same logic in Transform Data section, how would I do that.

thanks.

Highlighted
Super User III

## Re: Create new column based conditions of other two columns

You mean in Power Query? Just so you know, I am heavily heavily biased in providing answers in Power Query vs Calculated columns (links below) unless it doesn't make sense. And in this case, I do not think it does. This is an analysis that, IMHO, is best done in DAX using a calculated column or a measure. Power Query doesn't do well when scanning large tables "filtering" on the fly like this. For 10,000 rows, it doesn't matter. For 1,000,000 rows, Power Query may never finish. I am trying to think of a way using grouping or row selection that would avoid this but am coming up blank.

While I am not a fan of calculated columns, this is one of those instances where I think it will perform better than Power Query - unless someone with more skill than I (or imaginiation) can come up with a way. That said, this will work, but I will not swear it will perform well on large data sets. Test it and let me know. If you like it, it is preferable to Calculated Columns, all other things being equal.

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMLRU0lEyNDAAkapKsTogcWOYuBFY3AAmboKqHi5uChM3BosbwcTN0NTrGcK1mMOkTFFMssAubInDYkMDVJcaAyViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Column A" = _t, #"Column B" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column A", Int64.Type}, {"Column B", Percentage.Type}}),
RowData =
#"Changed Type",
"Current Data",
each
let
varCurrentDate = [Date],
varPreviousDate =
List.Max(
Table.SelectRows(
#"Changed Type",
each ([Date] < varCurrentDate and [Column B] <> 0)
)[Date]
)
in
if [Column B] = 0 then 0
else
List.Sum(
Table.SelectRows(
#"Changed Type",
each ([Date] = varCurrentDate) or ([Date] < varCurrentDate and [Date] > varPreviousDate and [Column B] = 0)
)[Column A]
),
Int64.Type
)
in
RowData``````

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

Please mark one or both as solutions, give kudos to thing that help, etc. 😁
I am most interested on the Power Query code on your full data set and how it performs.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Resolver I

## Re: Create new column based conditions of other two columns

Thank you for your help. The first solution is really good. The next one is a good starting point.

Highlighted
Super User III

## Re: Create new column based conditions of other two columns

great @rschaudhr - let me know how the Power Query solution works for you. I am curious over large record sets how it performs on refresh.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Resolver I

## Re: Create new column based conditions of other two columns

Just to let you know that your second solution is m query also worked out. My dataset was about 5K so it worked out well. The only downside to this is that it takes a long time to process. However it worked.

Thanks

Highlighted
Super User III

## Re: Create new column based conditions of other two columns

Yeah, 5K-10K records, not too bad. 100K, a pain. 1M? Unworkable. There are some things DAX is better at, and table scanning is one of them. It could do 1 billion records just as fast as 1 million.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021