Forum Discussion
Fill in Missing Dates for Multiple Rows
Hi all,
I've got a dataset that has four variables that serve as slicers (location, employee type, status1, and status2) by date. Our data system does not create rows for days with zero reports, so my dataset is some missing dates. I've incorporated a date table in Power BI and merged that into the original file so all dates in the range appear in my line chart, with zeros for the blank days.
I need these daily zero counts to appear on the chart regardless of the combination of slicer values selected. But, since there is no location, employee type, etc. attributed to these rows, the values on the line chart disappear when I click on the slicer selections. How can I make the zeros show for all permutations of slicer selections?
Here is the basic design of the report and source data layout.
The PBIX and sample source data are here.
Any guidance would be greatly appreciated!
3 Replies
Anonymous - So generally you handle that like this:
Measure = VAR __Calc = <some calculation goes here> RETURN IF(ISBLANK(__Calc),0,__Calc)Thank you. I tried the measure as suggested and it does not seem to work if any of the slicers are used. If no slicers values are selected, the zero dates appear fine. But when I click on a slicer value, the zero count days disappear, since there is nothing in the zero day data row to correspond to the slicer values of location, type, etc. values being filtered on.
Anonymous - It sounds like you might want to be using a disconnected table for your slicer. If you do that, then you have much more control over what gets displayed and what does not get displayed.
