TABLE ZERO AND DRILLDOWN

[SQUILES]

I have a situation and a need a suggestion.

I have a table that counts the records and use the feature drilldown. The situation is that if there are no records, the category does not appear and I need at least zero but without losing the drilldown.

My Data:

CATEGORY	SUB_CATEGORI	NAME	ITEM
A	NORTH	JOHN	3
A	NORTH	MARY	6
A	WEST	LOUIS	2
B	WEST	CHARLES	8
B	SOUTH	JOHN	4
D	SOUTH	KELLY	5

MATRIX OUTPUT without loose DRILLDOWN feature: (right click in the number to see records)

	A	B	C	D
NORTH	2	0	0	0
WEST	1	1	0	0
SOUTH	0	1	0	1
EAST	0	0	0	0

I try: 

Using a count the C column and EAST row not apprear beacuse everything is cero, but I have drilldown for all other. If I use a DAX I get cero value, but I loose the drilldown feature.

Any suggest?

[v-xicai · Accepted solution]

Hi @SQUILES ,

Based on my test using DAX to create a measure, and put it into Values box of Matrix visual,  there is drilldown feature as usual.

Since there is no CATEGORY "C" and SUB_CATEGORY "EAST" in  your original data table, it won't appear this zero row in the Matrix table in fact .

As a workaround, you may create two separated tables by "Enter Data" button to get all the values of CATEGORY and SUB_CATEGORY, create relationships with data table with "Both" Cross filter direction.

Then drag table 'SUB_CATEGORY'[SUB_CATEGORY] into Rows of Matrix, 'CATEGORY'[CATEGORY] into Columns, let both Rows and Columns be "Show items with no data",  setting Values as Count of CATEGORY.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

You add + 0 to your measure

Measure = sum(Table[Item]) +0

Also, use this option if needed.