Getting zero all rows instead of replacing only blanks with zeros

[ahmadhatahet]

Dear Community,

I have this simple report connecting to 3 tables in an excel file Projects, Milestones and Updates

Where each projects has multiple milestones and each milestone has an updates associated in the update table.

Each engineer add new record in the update table, project name, milestone, date and new percentage

giving me the ability to get the MAX update percentage for each milestone

Very simple "MAX no Zero = CALCULATE( MAX (update[update] ) )"

BUT, when I try to show the 0 instead of blanks in the milestones, where there are no updates for this milestones in the update table

I get repeated data as in the image, even my relationship is placed correctly.

MAX All Zeros = CALCULATE( MAX (update[update] + 0 ) )

I want to be able to drill down to a date level, where I can view how milestones updated throw time.

How can I show the max percentage for each milestone and 0 if the milestone has no records in the update table without any wrong repetation?

Thank you

[Fowmy]

@ahmadhatahet 

You can try:

MAX All Zeros = MAX (update[update] )  + 0

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

[ahmadhatahet]

Thank you, this as same as mine.

[mahoneypat · Accepted solution]

A few comments/suggestions:

1. You don't need to put CALCULATE around those expressions.  Just MAX() will work in this case.

2. If you project status only ever improves, your approach with MAX will work, but if project ever have setbacks and the progress values goes backwards, your measure will still just be getting the max progress.

3.  The "+0" approach is useful sometimes.  In this case, it is evaluating the measure for all the Date values in the Update table and always returning at least 0, which is why you have the extra rows.  If you don't have any rows there, there will not be a date value either.  Would it work to use the TD column from the Milestones table instead?  You could then use a measure like this to return 0 if there are no rows

Status = IF(ISBLANK(COUNTROWS(Updates)), 0, MAX(Updates[Update]))

Regards,

Pat

[ahmadhatahet]

Thank you, I wrapped the function in MAXX, so I can iterate on the milstones table using your expression.

This gave me a closer much better results

MAXX( milestones , IF( ISBLANK( COUNTROWS( 'updates' ) ) , 0 , MAX( 'updates'[Update] ) ) )

Otherweise the expression itself acted as my original measure "MAX ('updates'[Update]) + 0"