Forum Discussion

espo4life's avatar
espo4life
Helper I
6 years ago

Power BI Charting Question

I have a situation where I want to show every month for this year on the X-axis, but if there is no data for a month, just leaves it blank (no 0 points or anything). Below is the chart I want, but I had to add 0 values to the table for Jan 2020 (circled in red).

 

 

If there is no data in the "weather" table for Jan 2020, I want Jan 2020 to be on the x-axis, but it blank on the chart.

Need Help

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi espo4life 

    first, create a calendar table like

    Calendar = CALENDARAUTO()

    or

    Calendar = CALENDAR(date(2020,1,1), date(2020,12,31) )

    then create a relationships between calendar table and your fact table by [Date] field and put Calendar[Date] field to x-axis of chart

      • az38's avatar
        az38
        Community Champion
        6 years ago

        espo4life 

        you also can create a colum in calendar table

        AvgTemperature = 
var _relatedTemperature = RELATED(WeatherHistory[AvgTemperature])
RETURN
IF(ISBLANK(_relatedTemperature); 0; _relatedTemperature)

        and put Calendar[Date] to X axis and new column to Y

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Two potential suggestions 

    1. Under the formatting options for the chart, fix the X-axis to your desired start date.

    2. Add a constant measure (e.g., Constant = 1) and add that to your chart.  Color it so it is not visible.  It would show in your legend, of course.  Not a great option, but a potential quick fix.  You could even hide that part of the legend with a little white rectangle.

     

    If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat