Forum Discussion
Get Count in Query Editor by 2 Columns
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 ?
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.
8 Replies
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.
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
