Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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?
You probably have to iterate over ID and sum up:
Active New = SUMX ( VALUES ( Table[ID] ), [Active] )
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?
yes.. create a new measure as I mentioned above and use that instead of Active..
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
Not fully sure if you understood me correctly. Please see the below screenshot of what I meant:
Is this what you are looking for?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |