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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

 

OS.PNG

 

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?

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Greeting fm HK:)

Thanks Lin, It works well for me.

 

Best, Dennis J.

@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

JustinDoh1_3-1639420224866.png

 

What am I missing?

 

Thanks.

Justin

Greg_Deckler
Super User
Super User

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Not applicable

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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