Get Count in Query Editor by 2 Columns

[DJKarma]

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 ?

[DJKarma]

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.

[v-rongtiep-msft · Accepted 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.

[DJKarma]

The Column worked perfectly. Thank you so much bro

[v-rongtiep-msft]

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.

[DJKarma]

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

[v-rongtiep-msft]

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.

[DJKarma]

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

?

[v-rongtiep-msft]

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.