Forum Discussion
Post ProdigyCan you group by multiple columns?
hi,
I have the following data:
FeatureKey IssueKey IssueStatus IssuePoints
abc-1 abc-5 Done 2
abc-2 abc-7 Backlog 5
abc-1 abc-8 Done 3
abc-1 abc-9 In-Porgress 1
When I group on FeatureKey using count I get:
FeatureKey Count
abc-1 3
abc-2 1
I need to also group by ones that are "Done", so it would look similiar to the following:
FeatureKey Count Count-DoneItems Count-NotDoneItems
abc-1 3 2 1
abc-2 1 0 1
I was thinking maybe I needed to do some sort of calculation before using the group by ?
Assuming your Count is a measure, you can use CALCUALTE to get the two additional values you are looking for.
Count = COUNTROWS ( YourTable )Count-DoneItems = CALCULATE ( [Count], YourTable[IssueStatus] = "Done" )Count-NotDoneItems = CALCULATE ( [Count], YourTable[IssueStatus] <> "Done" )
9 Replies
- jdbuchanan71
Super User
Assuming your Count is a measure, you can use CALCUALTE to get the two additional values you are looking for.
Count = COUNTROWS ( YourTable )Count-DoneItems = CALCULATE ( [Count], YourTable[IssueStatus] = "Done" )Count-NotDoneItems = CALCULATE ( [Count], YourTable[IssueStatus] <> "Done" )
EaglesTonyMy Count is a result of a group by:
= Table.Group(#"Renamed Columns", {"FeatureKey"}, {{"Count", each Table.RowCount(_), Int64.Type}})
- Ashish_Mathur
Hi,
Any reason to prefer a Power Query solution (M code) over a DAX solution (measure)?
- EaglesTony
This gives me a count of # of rows in the table of total records and total count of done and not done items, but I need it by each FeatureKey as mentioned above.
- jdbuchanan71
Make a measure to sum the count count column then you can use that in the CALCUALTE measures.
- EaglesTony
I would think I use calculate first and then count ?
- EaglesTony
I ended up using a new column:
TotChildCount =var curParent = Merge1[Key]var final = COUNTROWS(FILTER(Merge1, Merge1[Key] = curParent))Return IF(final = BLANK(),0,final)andTotChildCountDone =var curParent = Merge1[Key]var final = COUNTROWS(FILTER(Merge1, Merge1[Key] = curParent && Merge1[Issues.ISSUE_STATUS_NAME] = "Done"))Return IF(final = BLANK(),0,final)
