Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Aggregating measures -

Hey All,

 

I have a table basic data table like below;

 

Consents
IDStatusConsent Date
1Active22/01/2019
1Active28/01/2019
1Expired28/12/2018
2Revoked09/01/2019
2Revoked06/03/2019
3Active26/03/2019
4Expired08/02/2019
4Expired17/12/2018
5Active18/03/2019
5Active25/02/2019
5Revoked13/12/2018
5Expired24/03/2019
5Active06/03/2019
6Revoked12/02/2019
6Expired08/01/2019
6Revoked29/03/2019

 

Currently this data table produces the below using the quoted measure below, which counts the number of statuses for each unique ID.

 

Aggreagated count.JPG

 

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

 

1 ACCEPTED SOLUTION

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

 

pbi.png

Is this what you are looking for?

View solution in original post

5 REPLIES 5
AkhilAshok
Solution Sage
Solution Sage

You probably have to iterate over ID and sum up:

 

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

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

 

Aggreagated count.JPG

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

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

Anonymous
Not applicable

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

Aggreagated count.2.JPG

I am after a table which produces the following;

Aggreagated count.JPG

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

 

thanks

 

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

 

pbi.png

Is this what you are looking for?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.