Blank cells in crosstab not converting to 0 despite IfNull / Coalesce all being tried
Issue:
I have a Matrix Grid showing "Work Time" by Worker (rows) crossed with Activity Date (columns). For Worker + Date combinations where there is no underlying fact record (i.e., the worker logged no activity that day), the cell renders as blank/null instead of 0.
Example: in the attached screenshots, several workers show blank cells for most dates, with a value only appearing on days where activity exists. I need these blank cells to display 0 instead.
What I've already tried (all unsuccessful — cells remain blank, not 0):
IF([Measures].[DAILY_USER_SUMMARY WORK_TIMESPENT] > 0, [Measures].[DAILY_USER_SUMMARY WORK_TIMESPENT], 0)IfNull([Measures].[DAILY_USER_SUMMARY WORK_TIMESPENT], 0)If(IsEmptyQuery(), 0, [Measures].[DAILY_USER_SUMMARY WORK_TIMESPENT])Coalesce([Measures].[DAILY_USER_SUMMARY WORK_TIMESPENT], 0)→ ran without error, but cells are still blank, not 0.
Can you please guide to me solve this in Formula
Thank you
2 replies
-
They render blank because the records don't exist. "Zero" means they do exist with a value of "0". This is the correct treatment.
You can try changing the inner joins to Left (or Right) joins between the worker dimension table and fact table in the semantic model.
-
Hi ,
Although is correct about the root cause of the issue, if you can not change the model due to valid considerations here is a little hack you could use.
you need to provide an actual column name that is used in the discovery for the [Table].[Dim]
IfNull([Measures].[DAILY_USER_SUMMARY WORK_TIMESPENT], 0)+ ( [DAILY_USER_SUMMARY WORK_TIMESPENT], AllMembers( [Table].[Dim] ) )- ( [DAILY_USER_SUMMARY WORK_TIMESPENT], AllMembers( [Table].[Dim] ) )The AllMembers([Table].[Dim]) forces a kind of full join that provides a place holder for the logic to be placed.