Forum Discussion

PowerPaddy's avatar
PowerPaddy
Frequent Visitor
9 years ago
Solved

COUNTX(currentgroup( ) with multiple conditions

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

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hmm,

     

    I guess this should work

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

    Hope this works

     

    Regards

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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!