Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
xRTP
Helper V
Helper V

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
hnguy71
Memorable Member
Memorable Member

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

hnguy71
Memorable Member
Memorable Member

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.