Line chart formatting issues - color choices and blank values

[Anonymous]

I have a line chart with two problems I'd like to solve.

1. The custom theme we use includes grey and white. We want charts to be on a white background, but neither of those colors shows up well. How do we have Power BI ignore certain colors when assigning the colors on a chart, while still using the other theme colors?

2. We don't have data every day, so on days with no data it just shows a dot, and the lines don't connect. I've been told I need to fill 0 values into each day in the data where we currently have no data. Is that true, and if so, what's the cleanest way to do that? Presumably using DAX but I can't figure it out.

Here's the chart which shows both issues described above:

Thanks!

[v-lid-msft]

Hi @Anonymous ,

We can change data color for every category if they does not meet your requirement.

We can change the type of X axis to continuous if you does not have data on every day.

BTW, pbix as attached.

Best regards,

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

[Anonymous]

@v-lid-msft  Thanks for the reply. The problem with changing to continuous is that I lose the daily detail, which the client wants to see. Now it only shows weekly values, although the lines do work now:

So it seems to me that I do need a data set which includes a day and value (even if value = 0 when not available), so that it can plot each day and still draw the line. Does that make sense? I would assume some DAX expression must be possible to make that happen?

Secondly, changing colors for categories to avoid white and grey won't work, because the categories constantly shift, this displays a top 5 of hundreds of possible values, so I can't program each one separately. As suggested above, I think I'll edit my theme file to remove white and grey - unless you know of a better solution? I'd rather keep the theme file as is, since I use those colors all the time anyway.

Thanks!

[mwimberger]

Hi @Anonymous 

The solution that @v-lid-msft proposed is exactly what I was going to post.

But, you aren't losing the detail of the days in the data being plotted - it is in the labelling of the axis that groups it automatically by weeks. Its the way the continuous axis auto fits items on the axis. If the range of dates is longer - it would show months, maybe even years, if you choose a shorter interval, it will show days or intervals of days ( say every two days)

So a couple of things that may help additionally:

Use a custom date table and set this as a date table

Make sure the date column being referenced in the x axis is set to a date format that does not have time in it, else it will show 23 Jan 12AM , 23 Jan 12 PM for example.

Below are a few examples using a timeline slicer to show the effect of longer or shorter periods to plot. Again, the plots are still showing daily detail, its just the axis that is being labelled by month, week, or even every two days.

Hope this helps

Cheers

Manfred

[mwimberger]

Hi @Anonymous 

First part of your question - easiest way is to edit the offending colours out of the theme file. This will be under the dataColors part of the JSON file. 

As for the second part I will create an example file which will hopefully help

Cheers

Manfred

[Anonymous]

@mwimberger  The only problem with taking them out of the theme file is that I use them constantly while formatting visuals, I just don't want them in the data areas of charts. But I guess since one of them is white (which will always be on the palette anyway), and the other will show in recently used colors once I create it once as a custom color, that I should be able to work around that issue. Thanks for the tip, I think I can make this work!