Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thank you in advance.
Solved! Go to Solution.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you for your help. The first solution is really good. The next one is a good starting point.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinggreat @rschaudhr - let me know how the Power Query solution works for you. I am curious over large record sets how it performs on refresh.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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?
Yes, it is cummulative based on Date. However, the condition are set based on column B.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |