COUNTA Dax function

[romovaro]

Hello

I need to create a formula counting for CID....How many contains Task "Partner Acknowledgement....." and have a schedule date

Tried with this one.... but 

Tasks with GLD & Closed = CALCULATE(COUNTA(CELERGO_12062021V1[SCHEDULED]),CELERGO_12062021V1[TASK]="partner Acknowledgement")..... but the problem is that "parnter Acknowledgment changes for every row" I need to use the formula "contains..." but I am not able to do it.

ANd then I need to answer the same question but with the task status CLosed

Tasks with GLD & Closed = CALCULATE(COUNTA(CELERGO_12062021V1[SCHEDULED]),                     (Need to use the contain formula for the "partner Acknowledgment).......and CELERGO_12062021V1[TASK_STATUS]="Closed")

CLIENT_NAME	CID	INSTANCE_NUMBER	TASK	TASK_STATUS	SCHEDULED
Client A	380249	1042784	Partner Acknowledgement - 991342 - 380249BE01	Received	04-jul-22
Client B	11942	1182224	Partner Acknowledgement - 1182411 - 381229	Received	31-ene-22
Client C	380310	1122325	Partner Acknowledgement - 1107560 - 380310GB01	Received	01-mar-22
Client E	11021	1186659	Partner Acknowledgement - 1186229 - 381236	Received
Client D	1000248	1163652	Partner Acknowledgement - 1160101 - 1000248FR01	Received
Client D	1000248	1164153	Partner Acknowledgement - 1160101 - 1000248DE01	Closed	02-may-22
Client D	1000248	1164154	Partner Acknowledgement - 1160101 - 1000248GB01	Closed	02-may-22

Thanks

[Samarth_18 · Accepted solution]

Hi @romovaro ,

You can try below code:-

Tasks with GLD & Closed =
CALCULATE (
    COUNTA ( CELERGO_12062021V1[SCHEDULED] ),
    CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" )
        && CELERGO_12062021V1[TASK_STATUS] = "Closed"
)

Thanks,

Samarth

[Samarth_18 · Accepted solution]

@romovaro , Just small correction it should be like this:-

Total Tasks =
CALCULATE (
    COUNTA ( CELERGO_12062021V1[TASK] ),
    CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" )
)

[romovaro]

HI Samarth,

Thanks a lot. I just had a meeting with management and below the final requirements...

I need to use the 3 formulas you gave me before but add an extra filter (Task = "IPM HandOff) and Task Status Closed

1) - Total Tasks = CALCULATE ( COUNTA ( CELERGO_12062021V1[TASK] ), CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" ) )+Extra filter

Showing Total of tasks that contains the string “Partner Acknowledgement” and have task “IPM HandOff” closed.

2) - Partner Acknowledgment with GLD = CALCULATE ( COUNTA ( CELERGO_12062021V1[SCHEDULED] ), CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" )) + Extra Filter

Showing Total of tasks that contains the string “Partner Acknowledgement” , have a schedule date and have task “IPM HandOff” closed.

Partner Acknowledgment with GLD & Closed = CALCULATE ( COUNTA ( CELERGO_12062021V1[SCHEDULED] ), CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" ) && CELERGO_12062021V1[TASK_STATUS] = "Closed" ) + Extra Filter

Showing Total of tasks that contains the string “Partner Acknowledgement” and task status Closed, have a schedule date and have task “IPM HandOff” closed.

Checked some previous forums and tried with your formulas but something is not working.

Total Tasks3 = CALCULATE ( COUNTA ( CELERGO_12062021V1[TASK] ), CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement", FILTER ( CELERGO_12062021V1, CELERGO_12062021V1[TASK] = "IPM HandOff" && CELERGO_12062021V1[TASK_STATUS] = "CLosed" ) ) RETURN IF ( ISBLANK ( result ), 0, result )

and last one (is it possible?)

I need to count all the tasks that have Task= Pre engagement process (Closed & received) and the IPM Handoff task (in the same column) Received...or missing (sometimes, the field IPM Handoff is not appearing...)

Pre Engagment needs to show all....and IPM HandOFF only the ones with Task_status Received...but sometimes the IPM HandOff is missing...any idea how to calculate that?

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

Final requirements... Thanks for everything

Robert

[romovaro]

Hi Samarth

last question for today 

If I want to calculate the ones that are 

Task = Pre Engagement Process & Task Status = Closed

and the ones that are:

task = IPM HandOff and Task Status = Open

Tasks Pre Engagement Received = VAR result = CALCULATE ( COUNT ( CELERGO_12062021V1[CLIENT_NAME] ), FILTER ( CELERGO_12062021V1, CELERGO_12062021V1[TASK] = "Pre Engagement Process" && CELERGO_12062021V1[TASK_STATUS] = "Closed" ) ) RETURN IF ( ISBLANK ( result ), 0, result )

and now I need to include task = IPM HandOff and Task Status = Open

I appreciate all your help

[Samarth_18 · Accepted solution]

If you want to include in same code then you can use this code:-

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

and if you need seperate one then below would be the code:-

Tasks Pre Engagement Received =
VAR result =
    CALCULATE (
        COUNT ( CELERGO_12062021V1[CLIENT_NAME] ),
        FILTER (
            CELERGO_12062021V1,
            CELERGO_12062021V1[TASK] = "IPM HandOff"
                && CELERGO_12062021V1[TASK_STATUS] = "Open"
        )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

@romovaro  You can ask as many question you want. 🙂

[romovaro]

Thanks 🙂

[Samarth_18 · Accepted solution]

Hi @romovaro ,

You can try below code:-

Tasks with GLD & Closed =
CALCULATE (
    COUNTA ( CELERGO_12062021V1[SCHEDULED] ),
    CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" )
        && CELERGO_12062021V1[TASK_STATUS] = "Closed"
)

Thanks,

Samarth

[romovaro]

HI Samarth,,

And if I just want to count all Partner Acknowledgment (No mather if a Schedule or not): Just to get the total of tasks.

Tring the Contain formula with count.... :s

Total Tasks = COUNTA(CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" ))

[Samarth_18 · Accepted solution]

@romovaro , Just small correction it should be like this:-

Total Tasks =
CALCULATE (
    COUNTA ( CELERGO_12062021V1[TASK] ),
    CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" )
)