cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerPaddy
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-huizhn-msft
Microsoft

Re: COUNTX(currentgroup( ) with multiple conditions

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

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: COUNTX(currentgroup( ) with multiple conditions

Hmm,

 

I guess this should work

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

Hope this works

 

Regards

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Microsoft v-huizhn-msft
Microsoft

Re: COUNTX(currentgroup( ) with multiple conditions

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

View solution in original post

CheekyData
Frequent Visitor

Re: COUNTX(currentgroup( ) with multiple conditions

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!

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors