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