Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
4 years ago
Solved

Count 2 requirements same column

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_NAMECIDINSTANCE_NUMBERTASKTASK_STATUS
Customer A380263997543IPM HandOffClosed
Customer A380263997543Resource AllocationClosed
Customer A3802631041927Partner Assignment  Closed
Customer A3802631041924Partner Assignment  Closed
Customer A380263997543Pre Engagement ProcessClosed
Customer B3802631041935IPM HandOffReceived
Customer B3802631041933Partner Assignment  Received
Customer B3802631041934Partner Assignment  Received
Customer A380263997543Client System SetupClosed
Customer A380263997543Pre Engagement ProcessClosed

 

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,
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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 )

4 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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

  • romovaro's avatar
    romovaro
    Responsive Resident
    4 years ago

    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's avatar
      Samarth_18
      Community Champion
      4 years ago

      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 )
  • romovaro's avatar
    romovaro
    Responsive Resident
    4 years ago

    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" }