Forum Discussion
Show zero on Line chart where no data for date
Hello all. I am trying to build a line chart visual that will display Zero when no data is present. I have been reading through the forum for some time and have tried multiple ways suggested and cannot get the chart to display the way I need. I am tracking audits performed by shift. On 03/16/21, the "Orange" shift did 1 audit and the "Blue" shift did none. I do not want to see a gap in the line for the "Blue" shift, but rather have it drop down to zero on the X- Axis.
The chart should look something like this:
I am using the following as my Measure to calculate the Audit Count:
AuditCount = COUNT(Audits[AuditID])+0
My X-Axis is Categorical as I want to display all dates. Changing to Continuous does not show the desired results as the Blue shift data line jumps from 03/15/21 to 03/17/21 and I do not want that.
Any suggestions / support to resolve this would be appreciated. Thank you.
Anonymous
Ok, here is a true and tested way:
Create a measure along the lines of:
Cutoff = VAR _MaxDataDate = CALCULATE(MAX('Audits'[ShiftDate]), ALL('Audits')) RETURN IF(MAX('Calendar'[Date]) <= _MaxDataDate, 1)Now select the visual and add this measure to the "Filters on this visual" in the filter pane and set the value to 1.
Use the proven [AuditCount] measure in the visual:
PaulDBrown as a follow-up to my reply yesterday, I came up with a different solution. As well as yours worked, it did not work for what I need to show. What I ended up doing is adding two calculated columns to my Audit Table.
CountO = If(Audits[Shift]="O",1,0) CountB = If(Audits[Shift]="B",1,0)and added them to my visual. I got the chart and the matrix to display with zeros as I wanted and no unwanted dates.
Thank you for putting up with me. 🙂
24 Replies
PaulDBrownCommunity Champion
Anonymous
Are you using a Date Table covering the range of dates in your model and with continuous dates?
- PaulDBrown
2 here is what I get using the date table for the x-axis and setting the X axis to categorical
The top chart shows a simple SUM; the bottom shows SUM + 0
Syndicate_AdminAdministrator
Hello, good afternoon
I have a case similar to the problem posed, I include zeros where there are no records, but I need it to show me only the projects in which the employee has participated, not all the projects I have in the data model.
How could I do it?
Since it shows me all the projects, but it does not respect the employee filter.
as I can attach pbix, so that you can help me.Regards, Thank you very much.