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 

Try either:

AuditCount = 
VAR _count = COUNT(Audits[AuditID])
RETURN
_count + 0

or 

AuditCount = COUNT(Audits[AuditID])

and then 

AuditCount = [AuditCount] +0
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Option 1 does not work... will try option 2, tomorrow.... I presume these are two separate measures each with a unique name?

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

    Correct, they are sepearate measures (sorry about repeating the name)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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:

       

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Anonymous 

         

        I don't know what the problem is at your end. Using a simple data sample I get this for

        Count = COUNT('Table'[Value])

        Measure = COUNT

        And for this: 

        Count + 0 = COUNT('Table'[Value]) + 0

        I get this:

        Measure = COUNT + 0

         

         

        And to compare them:

         

        Both have the x-axis set as categorical and the date is from the date table