Forum Discussion

romovaro's avatar
romovaro
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 =...
Need Help
  • Samarth_18's avatar
    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's avatar
    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's avatar
    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. đź™‚

romovaro's avatar
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