Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SQUILES
Helper I
Helper I

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:

CATEGORYSUB_CATEGORINAMEITEM
ANORTHJOHN3
ANORTHMARY6
AWESTLOUIS2
BWESTCHARLES8
BSOUTHJOHN4
DSOUTHKELLY5

 

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

 ABCD
NORTH2000
WEST1100
SOUTH0101
EAST0000

 

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?

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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.

92.png

 

 

 

 

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.

93.png

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.

View solution in original post

2 REPLIES 2
v-xicai
Community Support
Community Support

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.

92.png

 

 

 

 

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.

93.png

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
Super User
Super User

You add + 0 to your measure

ShowItemwithoutdata.JPG

Measure = sum(Table[Item]) +0

 

Also, use this option if needed.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.