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
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
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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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