I am trying to find an expression to aggregate values based on a field in the values existence in another set.
For example:
- a user has multiple identifiers
- I have a table of multiple users
- I have a table of the identifiers related to my user
- I have another table of identifiers
- those identifiers link to ownership of a record.
I need to count the total distinct records belonging to my current user. In the same application I need to get aggregates across the rest of the data so I do not want to use a filter or section access to restrict the data.
So basically I am looking for something that will count distinct based on the intersection of a set with a smaller set. More succinctly in SQL I would write Select count(distinct record) from record join owners where ownerIdentifier in (list of identifiers)
As example data say I have:
| CurrentUserIdentifiers |
| 1111 |
| 2222 |
| Identifiers | ValueDataId | |
| 1111 | 12345 | |
| 1111 | 23456 | |
| 2222 | 34567 | |
| 2222 | 45678 | |
| 3333 | 56789 | |
| ValueDataId | D1 | D2 | ... | ... |
| 12345 | qwer | poiu | | |
| 23456 | asdf | nmhj | | |
| 34567 | zxcv | hjkl | | |
| 45678 | bvcx | yuio | | |
| 56789 | gfds | trew | | |
And I would like to count the distinct D2 values for the current user in a single expression, without limiting other available data.
