OData querying for sum of descendants with column = true

[dalui]

Hi Community -

I'm trying to get the count of bugs that were found by a client. There is a boolean field (Custom_FoundByClient) to be true when this is the case. I'm not sure how to specify it to only filter for Custom_FoundByClient = true or false. My snippet is below.

Thanks!

let
    Source = OData.Feed("xxx"
            &"$filter=WorkItemType eq 'Feature'"
            &" and State ne 'Cut'"
            &" and Descendants/any()"   
        &"& $select=WorkItemId,Title,WorkItemType,State,Custom_EstimateCompleted,Custom_EstimateType"
        &"& $expand=AssignedTo($select=UserName),Iteration($select=IterationPath),"        
            &"Descendants("
                &"$apply=filter(WorkItemType eq 'Bug')"
                &"/aggregate($count as CountBugs, Custom_FoundByClient with countdistinct as FoundByClientCount)"
            &"),
            ", 
        null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]),
    #"Expanded Descendants" = Table.ExpandTableColumn(Source, "Descendants", {"CountBugs", "FoundByClientCount"}, {"Descendants.CountBugs", "Descendants.FoundByClientCount"})
in
    #"Expanded Descendants"

[edhans]

Could you provide some sample data? We don't have access to your ODATA feed, so I don't know what you are looking at or what your code is acting upon.

[dalui]

Sure thing

Feature Table

ID	Type	FeatureName
1	Feature	Name 1
2	Feature	Name 2

Bug Table

ID	Type	Feature ID	Bug Name	FoundByClient
1	Bug	1	Big Bug	True
2	Bug	1	Medium Bug	True
3	Bug	1	Small Bug	False

I'm already able to roll up the bugs per feature but I need to count it based on FoundByClient. In the example above. I want the query to provide me with BugsFoundByClient count as 2.

Result

ID	Type	Feature Name	FoundByClientCount	FoundByInternalCount
1	Feature	Name 1	2	1
2	Feature	Name 2	0	0

Thanks @edhans 

[edhans · Accepted solution]

Ok, here you go. I think this will work. See this PBIX file. But here is what I did:

1. First I created a reference to the Bug Source table then did a grouping to do the count. Not sure if this reference was necessary. It is if you want the Bug table in your model. If you don't need it you can skip the reference part, but it does destroy the details of the bug table. This is what I got:
   1. 
2. Then I merged that table back with the Features table using the Feature ID. But I DID NOT expand it.
3. I added a custom column with the following formula:
   1. =Table.SelectRows([Bugs Found], each ([FoundByClient] = true))
   2. 
      1. Put in the formula above in a new column
      2. Returns a new nested table that if you click on the white area next to it you can see....
      3. that now it is just a small 1 record table for Feature ID 1 where FoundByClient is true. It would work for Feature ID 2, 3, 4, n....
4. Added another custom column but this time I said [FoundByClient] = false
5. Now I have 3 nested table columns. Original merge, and the 2 Found By Client tables, one true, one false.
6. Expanded the first Found by Client table and only kept the Count column. Immediately renamed it "Found by Client"
7. Expaned the second Found Internal table and only kept the Count column. Immediately renamed it "Found Internal"
8. Changed both of those colums to be integers. All data types are lost when using nested tables like this.
9. Selected both columns and replaced NULL with 0.
10. Selected ID, Type, FeatureName, Found by CLient, and Found Internal, then "Removed Other Columns"
11. Resulting table is:
    1. 

You can see the full M code in the PBIX linked to above.

[dalui]

Awesome! I dug around and I was able to also get the count by casting the value.

&"/aggregate($count as CountBugs, cast(Custom_FoundByClient eq true, Edm.Int32) with sum as FoundByClient, cast(Custom_FoundByClient eq null or Custom_FoundByClient eq false, Edm.Int32) with sum as FoundByInternal)"

[edhans]

Excellent! yeah, if you can find away to get the server to do the work for you, that is always the best method.