COUNTX(currentgroup( ) with multiple conditions

[PowerPaddy]

Hello Again,

I'm creating a new table that has a count of all the new policies by month...

PolCount = GROUPBY(
   PolicyData,
   PolicyData[Month Written],
   "Month Written Count", COUNTX(currentgroup(),PolicyData[TRANSACTIONCODE]))

However, the policy data has multiple transactions and I only want it to count those which are labled as "NEW".

How do I use the COUNTX function as a COUNTIFS function like I would in Excel?

Many thanks

[v-huizhn-msft · Accepted solution]

Hi @PowerPaddy,

I test @TomMartens solution, it works fine, please create the new table using the formula below.

PolCount = GROUPBY(
   PolicyData,
   PolicyData[Month Written],
   "Month Written Count", COUNTX(currentgroup(),IF(PolicyData[label]="New",PolicyData[TRANSACTIONCODE],BLANK())))

Please feel free to ask if you still have any other issues. If you have resolved your problem, please mark the right/helpful reply as answer.

Best Regards,
Angelia

[Anonymous]

Hi Angelia,

I know this is an old discussion but I am having issues with GROUPBY and am trying to replicate what you did. Could you walkthrough the logic behind the IF statement ? I understand counting under the condition that [label] = "New" but what does "...PolicyData[TRANSACTIONCODE],BLANK()" do?

Thanks!

[TomMartens]

Hmm,

I guess this should work

 
...
COUNTX(currentgroup(),
  IF(PolicyData[Something] = "New"
    ,PolicyData[TRANSACTIONCODE]
    ,BLANK()
  )
)

Hope this works

Regards