Aggregating measures -

[Anonymous]

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

[AkhilAshok]

You probably have to iterate over ID and sum up:

Active New =
SUMX ( VALUES ( Table[ID] ), [Active] )

[Anonymous]

I dont htink i was very clear, i need the sum of the Active column :

This should sum to a total of 3, is there anyway i can replicat this?

[AkhilAshok]

yes.. create a new measure as I mentioned above and use that instead of Active..

[Anonymous]

This replicates the active count measure i already have in place;

I am after a table which produces the following;

Where the totals should come to 3 on each Active New & Revoked/Expired.

thanks

[AkhilAshok · Accepted solution]

Not fully sure if you understood me correctly. Please see the below screenshot of what I meant:

Is this what you are looking for?