Can you group by multiple columns?

[EaglesTony]

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 ?

[jdbuchanan71]

Make a measure to sum the count count column then you can use that in the CALCUALTE measures.

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

and 

TotChildCountDone =

 var curParent = Merge1[Key]

 var final = COUNTROWS(FILTER(Merge1, Merge1[Key] = curParent && Merge1[Issues.ISSUE_STATUS_NAME] = "Done"))

 Return IF(final = BLANK(),0,final)

[EaglesTony]

I would think I use calculate first and then count ?

[jdbuchanan71 · Accepted solution]

@EaglesTony 

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

[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.

[EaglesTony]

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

I thought it would be easier to group by in Power Query.

[Ashish_Mathur]

It is much easier with a measure as already shown by another contributor.