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.
Hi I have following data
Domian | Descr | Date | Count | Cumulative |
A | Close | 01/01/2020 | 1 | 1 |
B | Open | 01/01/2020 | 0 | 0 |
C | Close | 01/01/2020 | 1 | 1 |
A | Close | 02/01/2020 | 2 | 3 |
A | Open | 02/01/2020 | 1 | 1 |
I have 1st 4 columns and I have to calculate column 5 - Cumulative in the measure to use it for a line graph.
I have to add numbers in the cumulative column where Domain and descr are the same for the previous date . Eg A domian / clos e descr should show cumlative of A domain / close descr of o1/01/2020 and 02/01/2020. How can this be done?
I am not very good with the multiple filters in DAX , hence requesting help.
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous - I love Power Query, but this is best done in DAX I believe as was your original course. Here is what I have done:
You can see it matches your expected "Cumulative Goal" values. Full measure text below.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMine uses variables @Anonymous. The EARLIER() function is no longer needed and is not recommended. It returns the same results, and 3 years ago, was the only way to do it. But variables, the VAR/RETURN construct, is easier to read and edit, and can actually improve performance where you might user EARLIER() multiple times in a measure.
@Anonymous is correct that you should NOT use this in a calculated column. You should avoid them as a rule.
You can do this in Power Query, but it is not easy, and must use the advanced editor. Here is the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOyS9OBdIGhvpAZGRgZADkGIJxrE60khOQ5V+QmoeuAoJBKpxhhijgNgXFIiMkJUZAbAxXArPICNOQWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domian = _t, Descr = _t, Date = _t, Count = _t, Cumulative = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Count", Int64.Type}, {"Cumulative", Int64.Type}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"Domian", "Descr"},
{
{
"All Rows",
each
let
varCurrentTable = _
in
Table.AddColumn(
_,
"Cumulative Total",
each
let
varCumulativeDate = [Date]
in
List.Sum(
Table.SelectRows(
varCurrentTable,
each [Date] <= varCumulativeDate
)[Count]
)
),
type table [Domian=nullable text, Descr=nullable text, Date=nullable date, Count=nullable number, Cumulative=nullable number, Cumulative Total = nullable number]
}
}
),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Count", "Cumulative", "Cumulative Total"}, {"Date", "Count", "Cumulative", "Cumulative Total"})
in
#"Expanded All Rows"
If you need this in a column, then yes, by all means, use this code. It returns the red column, next to your "Cumulative" goal in the sample.
I cannot guarantee this will be performant over hundreds of thousands, or millions of records. That is where DAX excels and Power Query tends to bog down, but over 10K records or so, no problem. Give it a try.
I would appreciate KUDOS for any posts in this thread that have helped you, and if one of my answers is the solution, please mark it as such. See the very bottom of this post for some reasons not to use a calculated column. Below are directions to use this code in Power Query if you are not accustomed to pasting raw code in.
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.
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
Creating a Dynamic Date Table in Power Query
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous - I love Power Query, but this is best done in DAX I believe as was your original course. Here is what I have done:
You can see it matches your expected "Cumulative Goal" values. Full measure text below.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis is what I did- different from yours, but worked
Running Total =
CALCULATE(
SUM('Table'[count]),
FILTER(
'Table',
'Table'[Domain]=EARLIER('Table'[Domain])
&& 'Table'[Descr]=EARLIER('Table'[Descr]) && 'Table'[Date] <= EARLIER('Table'[Date])
)
)
How this different than yours @edhans ?
P.S. Thanks for replying with a solutions
Mine uses variables @Anonymous. The EARLIER() function is no longer needed and is not recommended. It returns the same results, and 3 years ago, was the only way to do it. But variables, the VAR/RETURN construct, is easier to read and edit, and can actually improve performance where you might user EARLIER() multiple times in a measure.
@Anonymous is correct that you should NOT use this in a calculated column. You should avoid them as a rule.
You can do this in Power Query, but it is not easy, and must use the advanced editor. Here is the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXLOyS9OBdIGhvpAZGRgZADkGIJxrE60khOQ5V+QmoeuAoJBKpxhhijgNgXFIiMkJUZAbAxXArPICNOQWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Domian = _t, Descr = _t, Date = _t, Count = _t, Cumulative = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Count", Int64.Type}, {"Cumulative", Int64.Type}}),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"Domian", "Descr"},
{
{
"All Rows",
each
let
varCurrentTable = _
in
Table.AddColumn(
_,
"Cumulative Total",
each
let
varCumulativeDate = [Date]
in
List.Sum(
Table.SelectRows(
varCurrentTable,
each [Date] <= varCumulativeDate
)[Count]
)
),
type table [Domian=nullable text, Descr=nullable text, Date=nullable date, Count=nullable number, Cumulative=nullable number, Cumulative Total = nullable number]
}
}
),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Count", "Cumulative", "Cumulative Total"}, {"Date", "Count", "Cumulative", "Cumulative Total"})
in
#"Expanded All Rows"
If you need this in a column, then yes, by all means, use this code. It returns the red column, next to your "Cumulative" goal in the sample.
I cannot guarantee this will be performant over hundreds of thousands, or millions of records. That is where DAX excels and Power Query tends to bog down, but over 10K records or so, no problem. Give it a try.
I would appreciate KUDOS for any posts in this thread that have helped you, and if one of my answers is the solution, please mark it as such. See the very bottom of this post for some reasons not to use a calculated column. Below are directions to use this code in Power Query if you are not accustomed to pasting raw code in.
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.
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
Creating a Dynamic Date Table in Power Query
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank u so much for taking out time to explain this!!
Glad to help @Anonymous - hope the rest of your project runs smoothly!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis is best done in DAX IF AND ONLY IF it's got to be a measure, @edhans. Actually, in case of a measure THERE IS NO CHOICE but DAX. But I think it was meant to be a calculated column, which PQ is best for.
can u explain how can this be done in power query? Because currently I added a new column in dax
How can his be done in power query?
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |