Counting rows, multiples criteria - CALCULATE, FILTER & MAXA, FILTER

[Anonymous]

Hey,

I am new to Power BI and currently having some troubles with the DAX function CALCULATE(COUNTROWS;

So i have a table of data with three columns, ID, Status & consent date.

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

What I need to do is create a table which counts the number of statuses for each unique ID like below:

Status
ID	Final Status	RevocationExpiredDate	Active	Revoked	Expired
1	Active		2	0	1
2	Revoked	06/03/2019	0	2	0
3	Active		1	0	0
4	Expired	08/02/2019	0	0	2
5	Active		3	1	1
6	Revoked	29/03/2019	0	2	1

Logic for this table is as follows:

RevocationExpiredDate: if Active > 0,"", if not then look up the latest consent date from 'Consent'[Consent Date]

Final Status: if Active > 0, "Active", if not then look up [Status] using [RevocationExpiredDate] & [ID] as criteria

Active: Count "Active" statuses for each ID

I have tried the below which should work for the status counts;

Active =
CALCULATE (
    COUNTROWS ( 'Consents' ),
    FILTER (
        'Consents',
        FIND ( "Active", 'Consents'[Status],, 0 )
            && FIND ( 'Status'[ID], 'Consents'[ID],, 0 )
    )
)

but due to the size of data (200,000+rows) the formula never finishes loading, so is not a viable option

I also need a column to display the latest revocation/expiration date, where i would normally use a nested MAXIF excel formula, from some reading it would appear i need a MAXA nested with FILTER logic?

**Excel format

=IF([@Active]>0,"",
   MAX(
      MAXIFS(CONSENT[Consent Date],CONSENT[ID],Status[@ID],CONSENT[STATUS],"Revoked"),
            MAXIFS(CONSENT[Consent Date],CONSENT[ID],Status[@ID],CONSENT[STATUS],"Expired")
   )
)

^basically if ID has an active consent status, blank/null, if not find the latest date matching to that ID.

Finally i would need to display the Final Status which would use the following excel logic:

**Excel Format

=IF([@Active]>0,"Active",
   INDEX('CONSENT'[STATUS],
      MATCH(1,INDEX(('STATUS'[@ID]='CONSENT'[ID])*([@RevocationExpiredDate]='CONSEN'T[Consent Date]),0,1),0)))

^basically if ID has an active consent status, "Active", if not return the status matching the ID and latest revocation/expiration date.

I know this is a lot of requests in one, but worth asking the community.

Thanks

Sam

[Anonymous]

You may refer below measures:

Active = CALCULATE(COUNTROWS(Consents),Consents[Status]="Active")+0

RevocationExpiredDate = IF([Active]=0,CALCULATE(MAX(Consents[Consent Date])))

Final Status =
IF (
    [Active] > 0,
    "Active",
    CALCULATE (
        MAX ( Consents[Status] ),
        FILTER ( Consents, Consents[Consent Date] = [RevocationExpiredDate] )
    )
)

[Anonymous]

@v-cherch-msft 

How would i go about changing the active measure to return just a 1 if positive, instead of the count? 

Active = CALCULATE(COUNTROWS(Consents),Consents[Status]="Active")+0

I have tried logic against the final status measure:

Active = IF([Final Status]="active",1,0)

however this figure is not aggregated in my tables:

Thanks

Sam

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may refer below measures:

Active = CALCULATE(COUNTROWS(Consents),Consents[Status]="Active")+0

RevocationExpiredDate = IF([Active]=0,CALCULATE(MAX(Consents[Consent Date])))

Final Status =
IF (
    [Active] > 0,
    "Active",
    CALCULATE (
        MAX ( Consents[Status] ),
        FILTER ( Consents, Consents[Consent Date] = [RevocationExpiredDate] )
    )
)

Regards,

[Anonymous]

Hey @v-cherch-msft ,

Thanks for replying, although i need the stats returned to be ID specific. currently this counts all Active status in the entire table, same goes for the RevocationExpiredDate.

thanks

sam

[v-cherch-msft]

Hi @Anonymous 

If it is not your case,could you explain more about your expected output?Please use the sample data as the example.Attached the file for your reference.

Regards,

[Anonymous]

Wow, im an idiot, i was creating a table in the data tab. just discovered measures. Your answer has solved all my problems.

thanks!