Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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,
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.
Proud to be a 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
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!!
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |