Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Opal55
Helper II
Helper II

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.

 

Opal55_0-1616528223843.png

The chart should look something like this:

Opal55_1-1616528344459.png

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.

Opal55_2-1616528484697.png

 

Any suggestions / support to resolve this would be appreciated.  Thank you.

2 ACCEPTED SOLUTIONS

@Opal55 

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:

 

cutoff.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

@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.

Opal55_0-1617113805923.png

Opal55_1-1617113836366.png

Thank you for putting up with me.  🙂

 

View solution in original post

24 REPLIES 24
Syndicate_Admin
Administrator
Administrator

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.

PaulDBrown
Community Champion
Community Champion

@Opal55 

Are you using a Date Table covering the range of dates in your model and with continuous dates?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






yes, I have a date table:

 

Calendar = CALENDARAUTO()

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

include 0.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@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?

@Opal55 

Try either:

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

or 

AuditCount = COUNT(Audits[AuditID])

and then 

AuditCount = [AuditCount] +0




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

@Opal55 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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:

Opal55_0-1616592896962.png

 

@Opal55 

 

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 = COUNTMeasure = COUNT

And for this: 

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

I get this:

Measure = COUNT + 0Measure = COUNT + 0

 

 

And to compare them:

result.JPG

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi! @PaulDBrown I am stumped as well.... from everything I read yesterday and what we have discussed in this thread it should work... I am stumped as well.  In my sample file, I have the stripped down data in 4 columns, AuditID, Date, JobID and Shift.  Does that make sense?

 

Opal55_0-1616601420014.png

 

@Opal55 
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:

model.JPG

 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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






There are two tables like this:

 

Opal55_0-1616604778844.png

 

@Opal55 

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:

axis.JPG

Change the field on the x-axis to the date field from the Calendar table





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Okay, @PaulDBrown , below is my result.... but I don't want to show data for dates that have not happened yet:

Opal55_1-1616605810936.png

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?

 

 

@Opal55 

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 )

 

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown  Getting a "Parameter is not the correct type" error?

 

Opal55_0-1616606889243.png

 

@Opal55 
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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Sorry, @PaulDBrown , I feel like I am being a terrible pain, but now I am back to where I started:

 

Opal55_0-1616607617503.png

a gap in the Blue shift line and chart stops at March 23rd - last date for data in the Audit table.

@Opal55 

No worries, it's my fault for trying to cut corners.

So... Keep your original measure:

AuditCount = COUNT(Audits[AuditID])+0

And 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)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.