How to display non-existent data

[Anonymous]

Hello, I want to make the matrix shown below.
However, the data with zero count cannot be displayed.
"Display items without data" or
I couldn't even add 0 to the major formula.
"count = COUNT (table1 [category]) +0"

I want to know this solution.

thank you

target table

count = COUNT(table1[category])

count = COUNT(table1[category])+0

[Greg_Deckler · Accepted solution]

@Anonymous So, in your matrix, use the day and id columns from table2. Remove the relationship between the tables and create a measure like:

count = 
  VAR __day = MAX('table2'[day])
  VAR __id = MAX('table2'[id])
  VAR __count = COUNTROWS(FILTER('table1',[id]=__id))
RETURN
  __count + 0

[Anonymous]

thank you for your reply.
It's a great solution.
However, in the actual data, the slicer of table1 is applied.
So I want to keep the relation and put the column id of table1.
is it possible?
thank you.

[v-shex-msft]

HI @Anonymous,

Nope, the filter effect has a high priority than DAX expressions. These expanded parts will be filtered first instead of replace/expand by DAX formulas.

Regards,

Xiaoxin Sheng