cancel
Showing results for
Did you mean:
Helper I

## COUNT and SUM only if has no zeros

Hi guys,

I need a dax measure to show if STATE has achieved 100% of delivery for product A,B,C

My column "Delivered" is an calculated measure. 1 means delivered and 0 means failed. and I need a new column on the state level.

I have the table below:

 DATE STATE PRODUCT DELIVERED (DAX MEASURE) 27-Jul NSW A 1 27-Jul NSW B 1 27-Jul NSW C 0 27-Jul VIC A 1 27-Jul VIC B 1 27-Jul VIC C 1

I need this new dax measure showing VIC as 1 (achieved) and NSW as 0 (failed)  and ten sum all the values with 1.

 STATE NEW DAX MEASURE NSW 0 VIC 1

1 ACCEPTED SOLUTION
Community Support

Try this:

``````Measure =
SUMX (
'Table',
"NEW DAX MEASURE", SWITCH ( 'Table'[STATE], "VIC", 1, "NSW", 0 )
),
[NEW DAX MEASURE]
)
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Try this:

``````Measure =
SUMX (
'Table',
"NEW DAX MEASURE", SWITCH ( 'Table'[STATE], "VIC", 1, "NSW", 0 )
),
[NEW DAX MEASURE]
)
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User III

Assuming Delivered is a calculated column in the your table,  you could:

1. Place Table1[State] in the rows of a table visual

2. Create this measure and place it in the visual

``````NewMeasure3 =
SUMX (
DISTINCT ( Table3[State] ),
IF ( CALCULATE ( COUNT ( Table3[Product] ), Table3[Delivered] = 0 ) > 0, 0, 1 )
)``````

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Resolver III

Hi

You could create a new table using 'group by' in Power Query Editor:

And then create a measure on the table:

Measure = if(sum('Table'[SUM]) = sum('Table'[Count]),"Achieved","Failed")

Which will give you this:

Jo

Announcements