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. 🙂
Community ChampionAnonymous
I can't see how the number of columns would affect the result; after all, you are counting IDs (from a single cloumn).
However, I see from one of the visuals that the x-axis is from "ShiftDate". Is that your Date Table?
Here is the model from my example. The x-axis is the Date field from the Date Table:
The Date Table has conitnuous dates covering the range of dates in the model (so including the dates where there are no values in the Fact Table)
There are two tables like this:
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
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
Anonymous
Ok, but it seems you are using the ShiftDate field (from the 'Audits' table) as your axis instead of the date field from the Calendar table:
Change the field on the x-axis to the date field from the Calendar table
Okay, PaulDBrown , below is my result.... but I don't want to show data for dates that have not happened yet:
I would like the lines to stop at at the last date where there is data in the Audits table, but still show all the dates of the current month....is that possible?
- PaulDBrown
Anonymous
Sure, just use the following measure instead of the COUNT + 0 measure:
New measure = VAR _Count = COUNT(Audits[AuditID]) VAR _CountZero = _Count + 0 VAR _Lastdate = CALCULATE ( MAX ( Calendar[Date] ), FILTER ( Calendar, NOT ( ISBLANK ( _Count ) ) ) ) RETURN IF ( MAX ( Calendar[Date] ) <= _LastDate, _CountZero ) - PaulDBrown
Anonymous
That's because "Calendar" is a DAX function per se, so you need to specify that you are pointing at an actual table by using the proper table syntax of 'Calendar'...Try:
New measure = VAR _Count = COUNT(Audits[AuditID]) VAR _CountZero = _Count + 0 VAR _Lastdate = CALCULATE ( MAX ( Calendar[Date] ), FILTER ( 'Calendar', NOT ( ISBLANK ( _Count ) ) ) ) RETURN IF ( MAX ( Calendar[Date] ) <= _LastDate, _CountZero )If not, change the Calendar table name to 'Calendar Table' and adjust the syntax in the measure to the new name
Sorry, PaulDBrown , I feel like I am being a terrible pain, but now I am back to where I started:
a gap in the Blue shift line and chart stops at March 23rd - last date for data in the Audit table.
- PaulDBrown
Anonymous
No worries, it's my fault for trying to cut corners.
So... Keep your original measure:
AuditCount = COUNT(Audits[AuditID])+0And then create the new one using:
New measure = VAR _Count = COUNT(Audits[AuditID]) VAR _Lastdate = CALCULATE ( MAX ( Calendar[Date] ), FILTER ( 'Calendar', NOT ( ISBLANK ( _Count ) ) ) ) RETURN IF ( MAX ( Calendar[Date] ) <= _LastDate, [AuditCount] )This measure will include data up to the last date for data in the Audit table. If you want the cutoff at a different date, we need to change the _Lastdate variable accordingly (if so, let me know where you want the cutoff date)
PaulDBrown , so from my report, I have this measure:
+ This measure:
and my chart looks like this.... no change from above....😶
Hi PaulDBrown ... apologies for the delay in getting back to you... was getting pulled away from this project for a couple of days. Now that I am back at it, I have tried your method, above and am getting better results....
A few responses back you had told me to change the X-Axis to the Date field in the Calendar table.... but here's my dilemma... we don't work most weekends so I am getting Zero results for weekend dates because I have no data for these dates. Is there a work-around for that?
Here is my data in a matrix:
So in the line chart visual, I want to show Zero for B shift on March 16, but I do not want to show any data for March 20 & 21 as we were not working... same for all other weekend dates in March. But if I switch to show February, we did work on Feb 27, so would want to show that data in the line chart visual. Does that make sense?