Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count using different criteria on two columns

Helloo,

how to count entries on column "Mailbox" where status is = "Queried - Onshore", "Queried - PO", "Oustanding - Sent to Rudy", "Can't Book", "Pending - Audit", "Pending - KL Audit", "Outstanding", "Not Started"? Thanks guys!

My code hehe: 

Adhoc =
CALCULATE(
COUNTA('CA Mailbox'[Mailbox]),'CA Mailbox'[Mailbox] = "Adhoc"
|| 'CA Mailbox'[Status] = "Queried - Onshore"
|| 'CA Mailbox'[Status] = "Queried - PO"
|| 'CA Mailbox'[Status] = "Oustanding - Sent to Rudy"
|| 'CA Mailbox'[Status] = "Can't Book"
|| 'CA Mailbox'[Status] = "Pending - Audit"
|| 'CA Mailbox'[Status] = "Pending - KL Audit"
|| 'CA Mailbox'[Status] = "Outstanding"
|| 'CA Mailbox'[Status] = "Not Started"
) + 0
 


 

 

Need Help
Tutorial Requests
  • hnguy71's avatar
    hnguy71
    4 years ago

    Hi Anonymous ,

     

    Try this:

     

    Adhoc = 

VAR _MailType = "ADHOC"
VAR _CriteriaArr = 
{
    "Queried - Onshore",
    "Queried - PO",
    "Oustanding - Sent to Rudy",
    "Can't Book",
    "Pending - Audit",
    "Pending - KL Audit",
    "Outstanding",
    "Not Started"
}

RETURN

CALCULATE(COUNTROWS ('CA Mailbox'), 'CA Mailbox'[Status] IN _CriteriaArr,'CA Mailbox'[Mailbox] = _MailType)

8 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    Anonymous 

    Following measure should work for:

    Adhoc =
CALCULATE (
    COUNTROWS ( 'CA Mailbox'[Mailbox] ),
    'CA Mailbox'[Mailbox]
        IN {
        "Queried - Onshore",
        "Queried - PO",
        "Oustanding - Sent to Rudy",
        "Can't Book",
        "Pending - Audit",
        "Pending - KL Audit",
        "Outstanding",
        "Not Started"
    }
)



    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks but still show inaccurate report, it supposed to output a total of 8 entries but it shows 22 entries 😞 

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        Anonymous 

        Please share a dummy file with this calucaltion to check and understand your scenario 


  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    This should work

    Adhoc = CALCULATE(COUNTA('CA Mailbox'[Mailbox]),'CA Mailbox'[Mailbox] = "Adhoc",('CA Mailbox'[Status] = "Queried - Onshore"
|| 'CA Mailbox'[Status] = "Queried - PO"
|| 'CA Mailbox'[Status] = "Oustanding - Sent to Rudy"
|| 'CA Mailbox'[Status] = "Can't Book"
|| 'CA Mailbox'[Status] = "Pending - Audit"
|| 'CA Mailbox'[Status] = "Pending - KL Audit"
|| 'CA Mailbox'[Status] = "Outstanding"
|| 'CA Mailbox'[Status] = "Not Started"
))