Count 2 requirements same column

[romovaro]

Hi Power BI community

I need your help. I need to count how many customers (using the CID Column) meet these 2 requirements that are in the same column (Task Column):

TaskColumn: IPM HAndOFF & Pre Engagement Process 

Task_status Column is closed

Sample table below:

In that Case Customer A should count (2 requirements ok + task status closed) but Customer B no (task_status is Received, not closed)

CLIENT_NAME	CID	INSTANCE_NUMBER	TASK	TASK_STATUS
Customer A	380263	997543	IPM HandOff	Closed
Customer A	380263	997543	Resource Allocation	Closed
Customer A	380263	1041927	Partner Assignment	Closed
Customer A	380263	1041924	Partner Assignment	Closed
Customer A	380263	997543	Pre Engagement Process	Closed
Customer B	380263	1041935	IPM HandOff	Received
Customer B	380263	1041933	Partner Assignment	Received
Customer B	380263	1041934	Partner Assignment	Received
Customer A	380263	997543	Client System Setup	Closed
Customer A	380263	997543	Pre Engagement Process	Closed

Tried different formulas but nothing so far.. 

Tasks IPM HandOff & Closed = CALCULATE(

FILTER(CELERGO_12062021V1, CELERGO_12062021V1[TASK] = "Pre Engagement Process"), CELERGO_12062021V1[TASK] = "IPM HandOff")

Thanks,

[romovaro]

Thanks Samarth_18.

the formula is counting all the customers that has one of the 2 requirements?. Is it possible to adapt the formula to show only the ones (CID or Client_Name) that has the 2 requirements, maybe?

IN { "Pre Engagement Process" and "IPM HandOff" }

[romovaro]

Thanks Samarth_18.

It works :). Now I have a problem and is that I just realized that the total number is showing "double amount of clients"

The Same client (CID) goes to different processes...Pre Engagment team, Partner Assignment...and IPM Handoff

With this formula it counts all the times we have the 2 requirements....ANy way to count only Once?

I can use the filter and Ask POwer Bi to show only total = 2 (meaning 2 requirements are good)...but If I can remove duplicates?

thanks again

[Samarth_18 · Accepted solution]

Try this:-

Tasks IPM HandOff & Closed = 
VAR result =
    CALCULATE (
        DISTINCTCOUNT( CELERGO_12062021V1[CID] ),
        FILTER (
            CELERGO_12062021V1,
            CELERGO_12062021V1[TASK]
                IN { "Pre Engagement Process", "IPM HandOff" }
                && CELERGO_12062021V1[TASK_STATUS] = "Closed"
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

[Samarth_18]

Hi @romovaro ,

You can use code as below:-

Tasks IPM HandOff & Closed = 
VAR result =
    CALCULATE (
        COUNT ( CELERGO_12062021V1[CID] ),
        FILTER (
            CELERGO_12062021V1,
            CELERGO_12062021V1[TASK]
                IN { "Pre Engagement Process", "IPM HandOff" }
                && CELERGO_12062021V1[TASK_STATUS] = "Closed"
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

Output:-

Thanks,

Samarth