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_NAMECIDINSTANCE_NUMBERTASKTASK_STATUSSCHEDULEDClient A3802491042784Partner Acknowledgement - 991342 - 380249BE01Received04-jul-22Client B119421182224Partner Acknowledgement - 1182411 - 381229Received31-ene-22Client C3803101122325Partner Acknowledgement - 1107560 - 380310GB01Received01-mar-22Client E110211186659Partner Acknowledgement - 1186229 - 381236Received Client D10002481163652Partner Acknowledgement - 1160101 - 1000248FR01Received Client D10002481164153Partner Acknowledgement - 1160101 - 1000248DE01Closed02-may-22Client D10002481164154Partner Acknowledgement - 1160101 - 1000248GB01Closed02-may-22Thanks

Responsive Resident

4 years ago

Solved

COUNTA Dax function

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

Need Help

Samarth_18

4 years ago

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

4 years ago

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

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

Samarth_18

4 years ago

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. 🙂

7 Replies

Samarth_18
Community Champion
4 years ago
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
  Responsive Resident
  4 years ago
  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
    Community Champion
    4 years ago
    romovaro , Just small correction it should be like this:-
    Total Tasks = CALCULATE ( COUNTA ( CELERGO_12062021V1[TASK] ), CONTAINSSTRING ( CELERGO_12062021V1[TASK], "Partner Acknowledgement" ) )
romovaro
Responsive Resident
4 years ago
Thanks 🙂
romovaro
Responsive Resident
4 years ago
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
  Community Champion
  4 years ago
  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
Responsive Resident
4 years ago
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

Forum Discussion

COUNTA Dax function

7 Replies

Related content

COUNT , COUNTA DAX Function

DAX Studio space after function name

COUNT , COUNTA DAX Function

DAX function

DAX Info Functions Against Published Datasets