How to fill missing values with 0 in line chart

Question

I am trying to fill missing values with 0 in a line chart in my PowerBI.

In my line chart, my x-axis is `Table[month]' and my y-axis is 'Count of 'Table[id]' and it has legend 'Table[level]'

Table

month	id	level
jan	1	1
jan	3	2
feb	4	2

e.g. So I want 0 for Feb level 1 in the line chart, since it has no value in ^ data.

So I read this post https://community.powerbi.com/t5/DAX-Commands-and-Tips/Fill-in-missing-values-in-a-line-graph-with-0-while-respecting/m-p/2193331, and a measure

Measure = var _1= COUNT(Table[id]) +0
var _min = minx(ALLSELECTED(Table), Table[month])
var _max = maxx(ALLSELECTED(Table), Table[month])
return
CALCULATE(if(max(Table[month]) <_min || min(Table[month]) >_max , 0, _1))

But it does not solve anything because I think max(Table[month]) <_min || min(Table[month]) >_max is always false and the measure is always _1

I also tried create a different measure

Measure 2 = CALCULATE(if(ISBLANK(Table[month]), 0, COUNT(Table[Id])))

But I get error saying `A single value for column month in Table Table cannot be determined. This can happen when a measure formula refers to a column that contains many values'

Can you please help me with this issue? Thank you.

Umut K · Answer

here is the DAX solution :

We need to generate a joined table of levels and months (which I assume you have separate tables.)

Let's create Month Table (if you dont have yet)

Modelling --> New Table

Month Table = SELECTCOLUMNS('Table', "Month", 'Table'[month])

Then let's join them with the level table so you will have all levels with months

Modelling --> New Table

Table 2 = CROSSJOIN('level table', 'Month Table')

then merge them with your main table

Modelling --> New Table

    Table with Zeros =
VAR A =
    SELECTCOLUMNS (
        'Table',
        "id", 'Table'[id] + 0,
        "level", 'Table'[level] + 0,
        "month", 'Table'[month] & ""
    )
VAR B =
    SELECTCOLUMNS (
        'Table 2',
        "level", 'Table 2'[level] + 0,
        "month", 'Table 2'[Month] & ""
    )
RETURN
    NATURALLEFTOUTERJOIN ( B, A )

Ozan Sen · Answer

I recommend you to build your fact table with the missing data like this without trusting the DAX to take care of it:

Like This: Just leave the id field empty:

Creata a simple measure:

Total Count = COUNT('Test'[id])

Then create a table visual to see it; but in order to see the missing values, you need to right click month column on the visualizations pane, and check that "Show items with no data" is ticked.Like you can see in the picture below:

And If you want to see it on a line chart:

usergIl71 · Answer

I think this request is due to a data model that is not fit for purpose. I don't think there is a way to expand the evaluation context to go beyond what exists in a 1-table model. The issue you are seeing is that for 'Table'[month] = 'feb' && 'Table'[level] = 1, there exists no evaluation context, since the combination does not exist in your original table.

In that sense both @Ozan Sen and @Umut K bring out the two realistic options for addressing this issue:

Either expand the table to include blanks
- I don't think this is a good solution since it clutters up a fact table
Or persist an additional table to serve as the original evaluation context basis

My suggested solution for this exact problem, is to first add a simple calculated table that contains the unique values of the month column, but persisted in a separate table:

Months = DISTINCT ( 'Table'[month] )

Connect this to your original table using a relationship in the Model tab. The new table forms the 1-side of the relationship, the existing table forms the many-side:

With this model setup, we now have an evaluation context that includes all months, and all levels - adding several more additional tables to the data model is not required to solve this.

With this extra table in place, you can add a simple measure that simply counts rows in the original table, and adds on zero to force a value instead of returning blanks for the combinations that have no count (e.g. for combinations like Months[month] = 'feb' && 'Table'[level] = 1):

Count = COUNTROWS ( 'Table' ) + 0

Which you can use for your Y-axis. In the X-axis, use the Months[month] column. This will give you your desired result, without adding a lot of bloat to your model, while avoiding complex DAX expressions:

The robust, or natural, way of solving this type of issue would be for your fact table to include an actual date (not a month), and to add a calendar table to handle slicing by different granularities of dates.

How to fill missing values with 0 in line chart

3 Answers