How do I get a line graph to show zero when there are no values?

[Anonymous]

So I have three tables. Table 1 contains a bunch of information, but has a column with a full list of ID (PICK_ID) numbers. Table 2 contains similar information to Table 1, but its PICK_ID column is a subset of Table 1's PICK_ID list.

Each ID also has a date attatched to it in a separate column. The tables are related by the PICK_ID column. The 'Date' column in Table 2 is a subset of Table 1's.

I've also created a Calendar Table with a 'Date' column from 1/1/2020 to 3/16/2020. Table 1's 'Date' column contains the same dates as the Calendar Table's.

When I create a line chart with 'Date' and Count of 'PICK_ID' I get this

That would be fine and all, except that there are dates that don't have any values. When I change it to a table I do see that the dates that wouldn't be in Table 2 don't show up, which makes sense since Table 2's is a subset of Calendar Tables' 'Date' column.

My question is, how do I get the dates that aren't in Table 2's 'Date' column to show up in the graph with a value of 0?

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, just add "+0" after the formula of the measure.

For example:

Measure= <Expression> +0

By the way, you could also refer to this document:

https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data

Regards,

Lin

[Dennis_J_Ngan]

Greeting fm HK:)

Thanks Lin, It works well for me.

Best, Dennis J.

[JustinDoh1]

@v-lili6-msft 

I am trying to show zero on the Y axis (for certain date in X axis), but it still shows as 1.

This is a measure:

ClientID_M = (COUNT(vw_census_trend[ClientID])) + 0

What am I missing?

Thanks.

Justin

[Greg_Deckler]

Well, in your measure you could return 0 specifically instead of allowing it to return BLANK. There should be an option, maybe it is only for tables, when you click the drop down arrow for your x-axis to "Show values with no data" or something like that. Also, is your x-axis continuous or categorical? You could also potentially set your starting y-axis value to 0 (force it instead of letting it be automatic.

[Clinical_Epi]

The problem with simply adding + 0 is when using a date table to handle the data users want to see - in a dynamic chart (lets say 2 years of data with a 12 month view 1 x line for a monthly sum of the 12 months and the other line the previous 12 months) is that this +0 will expand the chart out to the full spread of data.... this wont filter out using side filters...

How do you handle a chart that is filtered to show just 12 months with a date table that encompases a dynamic 2 years?

[Anonymous]

@Greg_Deckler @v-lili6-msft 

I'm actually not using a measure. I'm still fairly new to Power BI and I'm still not sure when to create a new measure vs. when  to just create a new column or table. I did select the option to 'Show items with no data' and force the Y-axis to start at zero, but when I look at it as a table the 'PICK_ID' column as a blank. 

If either one could help me with setting up the measure I'd appreciate it 😀

[Clinical_Epi]

Measures are pretty easy start slow - plenty of you-tube vids, trick is to make sure the columns are formatted in Power BI (sometimes it shows as text or number even though it should be date) pleenty of you-tube dids on this so too hard to spend work hours helping unfortunately.

Back to the using IF statement or adding +0 to end of a measure = this only work if you are NOT using date filters to reduce the size of visible dates in the dashbaord - dynamically... 

I automate everything into the cloud so this ones a bug bear for me !!

PS: there is a glitch where if using a Weekday measure to handle this issue in powerbi March 2023 desktop, where it looks fine on desktop version... but once publihsed to the cloud the date with no data disappears again... arggghh...

PS: weekdate measure:

Week Start Date = 'Date'[Date] - WEEKDAY('Date'[Date],2)

...here I am creating a new column in my date table, and I'm telling the weekday function that I want "Sunday" to be the start of the week (go figure but its what the end users want).. alas even using something like this (below) works on desktop - but once published any dat for that end date doesnt show in the cloud... damnnit!

Week Start Date = 'Date'[Date] - WEEKDAY('Date'[Date],3)-1