Forum Discussion
Aggregating measures -
Hey All,
I have a table basic data table like below;
| Consents | ||
| ID | Status | Consent Date |
| 1 | Active | 22/01/2019 |
| 1 | Active | 28/01/2019 |
| 1 | Expired | 28/12/2018 |
| 2 | Revoked | 09/01/2019 |
| 2 | Revoked | 06/03/2019 |
| 3 | Active | 26/03/2019 |
| 4 | Expired | 08/02/2019 |
| 4 | Expired | 17/12/2018 |
| 5 | Active | 18/03/2019 |
| 5 | Active | 25/02/2019 |
| 5 | Revoked | 13/12/2018 |
| 5 | Expired | 24/03/2019 |
| 5 | Active | 06/03/2019 |
| 6 | Revoked | 12/02/2019 |
| 6 | Expired | 08/01/2019 |
| 6 | Revoked | 29/03/2019 |
Currently this data table produces the below using the quoted measure below, which counts the number of statuses for each unique ID.
Active Count = CALCULATE(COUNTROWS(Consents),Consents[Status]="Active")+0
I have added two additional measures:
Active = IF([Active Count]>0,1,0)
Revoked/Expired = IF(AND([Active Count]=0,OR([Revoked Count]>0,[Expired Count]>0)),1,0)
however they are not aggregating a total, how can i acheive this?
Thanks
Sam
Not fully sure if you understood me correctly. Please see the below screenshot of what I meant:
Is this what you are looking for?
5 Replies
You probably have to iterate over ID and sum up:
Active New = SUMX ( VALUES ( Table[ID] ), [Active] )
yes.. create a new measure as I mentioned above and use that instead of Active..