Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have to create such a column in PowerBI Query Editor
This is the base table --
Customer ID | Unit ID | Date | Stamped |
1 | XX | 1st jan | 1 |
1 | YY | 1st jan | 2 |
1 | XX | 2nd jan | 2 |
1 | YY | 2nd jan | 3 |
1 | XX | 3rd jan | 2 |
2 | CC | 3rd jan | 1 |
2 | AA | 5th jan | 1 |
2 | BB | 5th jan | 1 |
The logic is for Each Customer ID Per date ,it should give total of Stamped infront of every Unit ID.
Output should look like this --
Customer ID | Unit ID | Date | Final Stamp Count | Reason ( don’t need this column) |
1 | XX | 1st jan | 3 | ( Row 1st + Row 2nd ) |
1 | YY | 1st jan | 3 | ( Row 1st + Row 2nd ) |
1 | XX | 2nd jan | 5 | ( Row 3rd+ Row 4th ) |
1 | YY | 2nd jan | 5 | ( Row 3rd+ Row 4th ) |
1 | XX | 3rd jan | 2 | ( Row 5th Only ) |
2 | CC | 3rd jan | 1 | ( Row 6th Only ) |
2 | AA | 5th jan | 2 | ( Row 7th + Row 8th ) |
2 | BB | 5th jan | 2 | ( Row 7th + Row 8th ) |
So as you can see for Customer ID 1, both XX and YY will have total of there stamped infront of them (also based on date), and likewise for all other rows.
I have tried various combinations of groupby but i am unable to pinpoint this. Can you please help me out ?
PS I NEED THIS IN QUERY EDITOR
@tamerj1 , last time you provided logic on point, can you help me out with this one ?
Solved! Go to Solution.
Hi @DJKarma ,
Please have a try.
Create a measure.
Measure = calculate(SUM('Table'[STAMPED]),FILTER(ALL('Table'),'Table'[DATE]=SELECTEDVALUE('Table'[DATE])&&'Table'[customer is]=SELECTEDVALUE('Table'[customer is])))
Or a column.
Measure = calculate(SUM('Table'[STAMPED]),FILTER(('Table'),'Table'[DATE]=EARLIER('Table'[DATE])&&'Table'[customer is]=EARLIER('Table'[customer is])))
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have 91K rows , I need to process this on. Its still not done processing, its taking forever, despite filtering only to few entries . I appreciate your help, but is thr any alternative? I can do with a measure as well.
Hi @DJKarma ,
Please have a try.
Create a measure.
Measure = calculate(SUM('Table'[STAMPED]),FILTER(ALL('Table'),'Table'[DATE]=SELECTEDVALUE('Table'[DATE])&&'Table'[customer is]=SELECTEDVALUE('Table'[customer is])))
Or a column.
Measure = calculate(SUM('Table'[STAMPED]),FILTER(('Table'),'Table'[DATE]=EARLIER('Table'[DATE])&&'Table'[customer is]=EARLIER('Table'[customer is])))
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The Column worked perfectly. Thank you so much bro
Hi @DJKarma ,
I have created a simple sample, please refer to it to see if it helps you.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYqIABJGBkZG+ob6IL6hUqwORCYyElXGCC6DrMcIRQZZD0jGGKseY7gekBpnZ1QZQ7iMoyNCxhRFxskJQyYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"customer is" = _t, #"unit id" = _t, DATE = _t, STAMPED = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"customer is", Int64.Type}, {"unit id", type text}, {"DATE", type date}, {"STAMPED", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([#"DATE"])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "month", each Date.Month([#"DATE"])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "day", each Date.Day([#"DATE"])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each List.Sum(Table.SelectRows(#"Added Custom2", (x)=>x[Year]=[Year] and x[month]=[month] and x[day]=[day] and x[customer is]=[customer is])[STAMPED]))
in
#"Added Custom3"
If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I need to condition it according to my data, can you please give me the last formula to put it in here.
Assuming i have year, month,day columns already extracted from date.
That is when i goto and click add custom column. What should i put it in the box
Custom =
???
This formulae is based on custom2, but i am trying to click and add custom column.
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each List.Sum(Table.SelectRows(#"Added Custom2", (x)=>x[Year]=[Year] and x[month]=[month] and x[day]=[day] and x[customer is]=[customer is])[STAMPED]))
Hi @DJKarma ,
How about this?
List.Sum(Table.SelectRows(#"Added Custom2", (x)=>x[Year]=[Year] and x[month]=[month] and x[day]=[day] and x[customer is]=[customer is])[STAMPED])
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, for editing, as i am learning.
List.Sum(Table.SelectRows(#"Added Custom2",
When i put this query , it says Custom2 was not recognized, I am assuming I did extract columns from date and renamed to look exactly like yours. And my last instance was Renamed Columns. So shall i put it as--
List.Sum(Table.SelectRows(#"Renamed Columns", (x)=>x[Year]=[Year] and x[month]=[month] and x[day]=[day] and x[customer is]=[customer is])[STAMPED])
?
Hi @DJKarma ,
You are right. The red mark in the formula represents the previous step of the present step.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
227 | |
127 | |
118 | |
83 | |
78 |