Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RustyNails
Helper III
Helper III

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
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.