topic Help with blankrows and summarize in DAX Commands and Tips

Help with blankrows and summarize

ian_viana2 — Fri, 26 May 2023 05:00:53 GMT

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

Showing no values data

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,

Re: Help with blankrows and summarize

some_bih — Fri, 26 May 2023 07:38:31 GMT

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

Re: Help with blankrows and summarize

ian_viana2 — Fri, 26 May 2023 08:30:31 GMT

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

Re: Help with blankrows and summarize

some_bih — Fri, 26 May 2023 10:02:06 GMT

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.