cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xRTP
Helper III
Helper III

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
 


 

xRTP_0-1653767513294.pngxRTP_1-1653767649032.png

 

1 ACCEPTED SOLUTION

Hi @xRTP ,

 

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) 


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Fowmy
Super User
Super User

@xRTP 

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



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@xRTP 

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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi @xRTP ,

 

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) 


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

You really are a Sage! Thanks master!!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors