Count using different criteria on two columns

[Anonymous]

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

[Ashish_Mathur]

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

[Fowmy]

@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]

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

[Fowmy]

@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"
)

[Fowmy]

@Anonymous 

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

[Anonymous]

Hello @Fowmy 

Here ya go

https://drive.google.com/drive/folders/1Ipqht-1wSOMu2kcdx5oJ5f5CU4aqsOnT?usp=sharing 

[hnguy71 · Accepted solution]

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) 

[Anonymous]

You really are a Sage! Thanks master!!