Forum Discussion
TABLE ZERO AND DRILLDOWN
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?
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.
2 Replies
You add + 0 to your measure
Measure = sum(Table[Item]) +0
Also, use this option if needed.
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.
