Search code examples
powerbidaxpowerbi-desktop

Power Bi - Show Rows with No Values

I have 2 tables (i.e. TABLE1 and TABLE 2). Table1 contains storagebins and Table2 contains stock in that storagebins.

Table1
ID - STORAGEBIN
01 - B-0001
02 - B-0002
03 - B-0003

Table2
ID - STORAGEBIN - EAN
01 - B-0002 - 123456xx
02 - B-0003 - 123456yy
03 - B-0002 - 123456zz
04 - B-0002 - 123456yy

Now what I'd like in a visual/table in Power Bi is an output like this:
STORAGEBIN - TOTAL EAN
B-0001 - 0
B-0002 - 3
B-0003 - 1

I simply tried a visual 'table' and put in the column STORAGEBIN from TABLE1. Next to it I put the column EAN from TABLE 2 and aggregate to total. The problem is I don't get the number 0 for B-0001. I only get the storagebins which have some stock in them and not the bins that don't have stock in them (so simply: the empty storagebins).

Solution

  • Ensure there is a relationship between your two tables and then drag STORAGEBIN from table 1 into a table visual and add the following measure:

    COUNT('Table 2'[STORAGEBIN])+0

    Alternatively, click the field well drop down for you column:

    enter image description here

    And select show items with no data.

    enter image description here