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", "Outstandi...
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)
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 


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

      hnguy71 

      You need to add a filter on the MailBox for Adhoc and filter Status to given values

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