cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RustyNails Regular Visitor
Regular Visitor

Count all the items in a group if the group has a nonzero value

Hello team -

 

I'm trying to create a measure where it will count the number of items in a group only if that group contains a non zero number, including the zero. What I mean is that in this particular scenario:

 

 

I want to count all the number of items in Order ID 624 even though line number 9 is a zero. The logic would look at Order IDs, and determines if there is a non-zero number in there. On this step, 516 fails. But 624 passes. Then it just flags the Qty's as 1.

 

Any help would be appreciated. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count all the items in a group if the group has a nonzero value

If you want a column, then perhaps something like:

 

Flag = 
VAR __max = MAXX(FILTER(ALL('Table'),[Order ID] = EARLIER([Order ID])),[Item Qty])
RETURN
IF(__max>0,1,0)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Count all the items in a group if the group has a nonzero value

If you want a column, then perhaps something like:

 

Flag = 
VAR __max = MAXX(FILTER(ALL('Table'),[Order ID] = EARLIER([Order ID])),[Item Qty])
RETURN
IF(__max>0,1,0)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Highlighted
RustyNails Regular Visitor
Regular Visitor

Re: Count all the items in a group if the group has a nonzero value

Sir you are absolute genius. Thank you!

 

I was able to make it a measure by making a new measure = sum(Flag).

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 196 members 2,038 guests
Please welcome our newest community members: