cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Create new column based conditions of other two columns

DateColumn AColumn BColumn D - new column
1/2/20191001%100
1/3/20192000%0
1/4/20191000%0
1/5/20193002%600
1/6/2019100.1%100
1/7/2019500%0
1/8/2019500%0
1/9/20191000%0
1/10/20192003%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. 

 

Thank you in advance.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
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

edhans_0-1599589927269.png

 



Did I answer your question? Mark my post as a solution!
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

View solution in original post

Highlighted
Resolver I
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.  

View solution in original post

Highlighted
Super User III
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 = 
        Table.AddColumn(
            #"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

 

edhans_0-1599601567677.png

 

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
5) See this article if you need help using this M code in your model.

 

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 I answer your question? Mark my post as a solution!
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

View solution in original post

9 REPLIES 9
Highlighted
Continued Contributor
Continued Contributor

Re: Create new column based conditions of other two columns

@rschaudhr 

 

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
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
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

edhans_0-1599589927269.png

 



Did I answer your question? Mark my post as a solution!
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

View solution in original post

Highlighted
Resolver I
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.  

View solution in original post

Highlighted
Super User III
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 = 
        Table.AddColumn(
            #"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

 

edhans_0-1599601567677.png

 

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
5) See this article if you need help using this M code in your model.

 

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 I answer your question? Mark my post as a solution!
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

View solution in original post

Highlighted
Resolver I
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
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 I answer your question? Mark my post as a solution!
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
Resolver I

Re: Create new column based conditions of other two columns

@edhans 

 

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
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 I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors