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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ahmadhatahet
Helper I
Helper I

Getting zero all rows instead of replacing only blanks with zeros

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

 

 

1.jpg2.jpg3.jpg

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@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 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you, this as same as mine.

mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors