Forum Discussion
Show zero on Line chart where no data for date
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. 🙂
PaulDBrown : Unfortunately, that is not what I get. I am counting records, not summing them, as the data table shows the date of the audit, the area audited, the employee audited and the shift as well as an audit ID field. I am counting the Audit ID field as that is what I showed in my original post. Any other thoughts?
Community ChampionAnonymous
Try either:
AuditCount =
VAR _count = COUNT(Audits[AuditID])
RETURN
_count + 0or
AuditCount = COUNT(Audits[AuditID])
and then
AuditCount = [AuditCount] +0Option 1 does not work... will try option 2, tomorrow.... I presume these are two separate measures each with a unique name?
- PaulDBrown
Anonymous
Correct, they are sepearate measures (sorry about repeating the name)
Sorry, PaulDBrown , option 2 does not work either.... I still have a break in the line. I wish I could share the database, but I cannot do it from work. I have stripped it down and made a sample and tried both measures and get the following results in my stripped down version: