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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ian_viana2
New Member

Help with blankrows and summarize

Hi guys, I'm stuck in this measure for a while and would appreciate some help.

The measure is to check if the employee filled his timesheet ( IF([Total Hours]<Measures_[Standard Hours]*.75 || ISBLANK([Total Hours]),1)). I created the measure and everything was alright until a problem with a date slicer came up.
When we filter by month, the measure should also flag with 1 all the timesheets from that month, even if its in the future. The problem is that the rows with the future dates are seen as blank by power bi. My summarize is already on the fact table and I could create a measure that work on the future dates, the problem is that i can't merge them and have the total correct.

Power Bi Rows

ian_viana2_3-1685077186991.png

 

Showing no values data 

ian_viana2_2-1685077108165.png

 

 

Measure working(on non blank rows)
FILTER(    
SUMMARIZE('Time',
                      
                     
                      'Tran Date'[LastOfWeek],
                      'Tran Date'[Week End Date],
                      'Tran Date'[IsWorkingDay],
                      'Time'[Staff_ID],
                       'Staff Detail'[Left Date],
                       
                      "completed",
                     IF([Total Hours]<Measures_[Standard Hours]*.75 || ISBLANK([Total Hours]),1))
                
            ,  ('Staff Detail'[Left Date] > TODAY()-7 || ISBLANK('Staff Detail'[Left Date])) && 'Tran Date'[IsWorkingDay]=1)

Measure that works on blank rows:
SUMX (
VALUES('Tran Date'[LastOfWeek]),
CALCULATE (
IF (
[total hours] < [standard hours] * 0.75
|| ISBLANK(Measures_[Total Hours]),
1
)
)
)

The expected result is the measure counting 1 for the missing timesheet (even with future date) and the correct total number.

It isn't a problem if i need to select the "show values with no data" option, I just needed the measure also working on this rows.
thanks,





3 REPLIES 3
some_bih
Super User
Super User

Hi @ian_viana2 possible approach: in Power query, if you know, already that there should be some default value for blank rows in some columns you could simple do replace null / blanks with default value. Give a try and see how this affect your desirable output.
Measure / Power BI simply do what user instruct to do so it could be that your measure is not optimal, so try to include in column / measure both part for non-blank and blank rows with some if logic.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @ian_viana2 maybe you should check your usage of ISBLANK

Check link for possible your case https://dax.guide/isblank/  as  ISBLANK check that an expression is strictly equal to BLANK





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Hi, @some_bih. In the first measure I'm using isblank to check if the measure total hours is blank or not. That's why i don't understand why the row with future dates are not counting. I tried isblank with other columns as well and it didn't work. 
And the main thing, if the week_end_date columns is populated, why power bi is considering it as value with no data?

the only thing i can think about is the relationship between tables, but can't figure out what.

thanks for you help!!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors