Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ca...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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:

     

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.