Chart shows more years than in data

[ChristophEmrich]

Hi everybody!

I'm having a regular LINE AND STACKED COLUMN CHART, populated with data from the years 2008 to 2019. The years are on the x-axis of the chart. The problem is that one the chart I see the range from 2005 to 2022 on the x-axis with the years 2005 to 2007 and 2019 to 2022 empty, i.e., without values. How can I tell PowerBI to show only the years I have data for?

Thank you

Christoph

[vivran22]

@ChristophEmrich 

Hi Christoph,

There are two graph settings which could help:

Under Fields > Axis > Do not select the Date Hierarchy/Select the date instead
Under Format > X Axis > Type > Categorical (by default it is continuous)

Hope this should solve the purpose.

Thanks,

Vivek

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

[ChristophEmrich]

Thank you for the quick response.

Under Fields > Axis > Do not select the Date Hierarchy/Select the date instead

I don't find this in the visualization. You will find what I see in PowerBI in the attached picture. I should add that I have the "Year" as a whole number, not a date.

Under Format > X Axis > Type > Categorical (by default it is continuous)

If I do this, the empty years disappear but the sorting of the year reverses, i.e., 2019 is on the left of the axis and 2008 is on the right. Can I reverse this somehow?

[v-piga-msft]

Hi @ChristophEmrich ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output. Or, ideally, share the pbix (beware of confidential data).

Best  Regards,

Cherry

[ChristophEmrich]

Sorry for not replying sooner. I was on vacation. The problem unfortunately still persists (they somehow never go away during vacation...).

Anyway, I cannot post any sample files but I made a screenshot so maybe that helps. I have a slicer on the "Year" value and it correctly displays 2008 to 2027 because that's the full range of the data that I have. It's an integer field. The x-axis on the chart however shows 2005 to 2030. I'd like the chart to show only what's set in the slicer.

[vivran22 · Accepted solution]

@ChristophEmrich 

Hi,

Try this:

Go to the visual option> Sort by > Order Year

You need to convert the X axis to Categorical.

Rgds,

Vivek

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

[ChristophEmrich]

---

@vivran22 wrote:

@ChristophEmrich 

Hi,

Try this:

Go to the visual option> Sort by > Order Year

You need to convert the X axis to Categorical.

Rgds,

Vivek

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

---

That did the trick, thank you very much!

[vivran22]

Hi,

Can you please check and confirm the X axis setting of the chart - is it set to Categorical or Continuos?

If Continuous, then please change it to Categorical.

Rgds,

Vivek

[vivran22]

Hi please ignore the above. It was added to this post by mistake.

Rgds,
Vivek

[vivran22]

Is it possible to share the sample data/pbix file? I will have better understanding of the situation.

Thanks,
Vivek