Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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)
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"
))
@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"
}
)
⭕ 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"
)
⭕ 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
⭕ 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)
You really are a Sage! Thanks master!!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |