This should be easy I think.
I have an app that produces survey response data in a pattern something like the simple example shown. Each row is a response to a question, and each response is tagged with a "ResponseGroupID" that ties them together as having been entered as part of a single "batch". I'm finding it easy to aggregate individual response values (not shown below) but I'm having trouble simply counting how many "batches" (as identified by their ResponseGroupID) each location has.
Raw Data
Location | MeasureID | ResponseGroupID | Date |
6B | 1 | 1246 | 1/1/2020 |
6B | 2 | 1246 | 1/1/2020 |
6B | 3 | 1246 | 1/1/2020 |
5LW | 1 | 8954 | 1/1/2020 |
5LW | 2 | 8954 | 1/1/2020 |
5LW | 3 | 8954 | 1/1/2020 |
6B | 1 | 3389 | 1/5/2020 |
6B | 2 | 3389 | 1/5/2020 |
6B | 3 | 3389 | 1/5/2020 |
Table I would like
Location | Distinct ResponseGroupIDs |
6B | 2 |
5LW | 1 |
Table I get
Using a mega-table and adding location and my calculated field
Location | Distinct ResponseGroupIDs |
6B | 1 |
6B | 1 |
5LW | 1 |
The formula I'm using in 'Distinct ResponseGroupIDs is
COUNT(DISTINCT `ResponseGroupID`)
Not sure if I have to create separate grouped by tables in the data flow but I'm trying to avoid additional complexity when possible and this seems as though it should be simple. Using Sum(COUNT(DISTINCT `ResponseGroupID`) in the calculated field doesn't work. Should this be done in the SQL flow that produces the model?