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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.