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.
The chart should look something like this:
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.
Any suggestions / support to resolve this would be appreciated. Thank you.
Solved! Go to Solution.
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. 🙂
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.
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
@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?
AuditCount = VAR _count = COUNT(Audits[AuditID]) RETURN _count + 0
AuditCount = COUNT(Audits[AuditID])
AuditCount = [AuditCount] +0
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:
I don't know what the problem is at your end. Using a simple data sample I get this for
Count = COUNT('Table'[Value])
And for this:
Count + 0 = COUNT('Table'[Value]) + 0
I get this:
And to compare them:
Both have the x-axis set as categorical and the date is from the date table
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?
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)
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?
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 )
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'...
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.
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)
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.