Matrix not showing rows with measure equal zero

[arielcedola]

Hi,

I have a matrix showing the count of values in a given column of a table (column called TypeOfCustomer), filtered by Company by using a slicer. These values are 5 different categories, let's say A, B, C, D, E. So the matrix in principle should have always 5 rows, one for each category, and 2 columns, TypeOfCustomer and Count.

The point is that when a Company doesn't have customers of a given type, the corresponding row is removed from the matrix instead of simply showing zero, and I need this not happens.

I tried by adding 0 to the DAX measure and also by checking "Show items with no data", but they didn't work.

What is the most simple way to solve this?

Thanks!

[amitchandak]

Have you done show row with no data for all row and column fields ?

[arielcedola]

Hi @amitchandak,

thank you for your response. Yes I tried, actually only in Rows because I don't have nothing in Columns, just the Count measure in the Values field of the matrix. But it didn't work.

Ariel

[amitchandak]

@arielcedola 

Is it possible to share pbix, after removing sensitive information?

Some time measure filter can cause that. Just check the you measure formula

[MFelix]

Hi @arielcedola ,

If you are using a measure when there is no data the value returns blank to overcome this you need to add a + 0 to your measure.

Your measure should look similar to this:

Count = COUNT(Table[Column]) + 0

[PhilDanley]

Thank you Miguel. This fixed a matrix that I was working on. You smoothed out my morning!

[arielcedola]

Hi @MFelix,

thank you for your response. I tried but it didn't work. It seems reasonable because when filtering by a Customer for which one or more categories are not present, I guess the COUNT measure is not aware about their existence and performs the operation taking into account the categories that it sees.

Ariel 

[MFelix]

Hi @arielcedola ,

I used the count measure as a example you can use any measures. 

When you are refering to categories how are this categories on your model? Are they part of the same table or a different table related with the Customer?

If this is a related table remember to have this with many to one relationship and use this as your visual interaction and not the category on the other table.

[arielcedola]

@MFelix,

actually there is no relation with any other table, the categories are part of the same table along with Company. So the idea is to count the number of times a category is present in the TypeOfCustomer column, filtering by Company, and visualize it in a matrix, even when this count is equal to zero. Maybe I should create another table with these categories? In this case, how tables should be related?

[MFelix · Accepted solution]

Not @arielcedola,

From what you are explaining you have a table with the following data:

company	Client type	Client ID
A	Tipo1	C1
A	Tipo2	C2
B	Tipo1	C1
C	Tipo2	C1

And you want to display type 1 and type 2 with the following results if slicer is selected:

Filtering a company

guy	count
Type 1	1
Type 2	1

Filtering company B

guy	count
Type 1	1
Type 2	0

Enterprise C filtering

guy	count
Type 1	0
Type 2	1

Is this correct?

You must create a new table with the unique values of the types and make one to many from this table to your customers tables and then use this as your display axys or table column should work.

Check the PBIX file connection.

[arielcedola]

@MFelix 

great! thank you so much, it is just what I needed.

Best regards, from the very epicenter 😉

Ariel