cancel
Showing results for 
Search instead for 
Did you mean: 
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
Memorable Member
Memorable Member

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.

Sarajevo, Bosnia and Herzegovina
some_bih
Memorable Member
Memorable Member

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

Sarajevo, Bosnia and Herzegovina

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
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors