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
Dehlilah
Regular Visitor

Displaying "0" in table or matrix

Hello, 

 

I created a code to sum sub-categories and the code works. However, I want to visualize areas that have zero as a value as this is important to know regions without. 

 

Here is my code: 

 

Remaining =

VAR Total =

    CALCULATE(

        SUM('Table'[Column1]),

        FILTER(

            'Table',

            'Table'[Column 2] = "x1" ||

            'Table'[Column 2] = "x2" ||

            'Table'[Column 2] = "x3"

        )

    )

RETURN

    IF(

        ISBLANK(Total),

        0,

        Total

    )

 

I have tried adding "<>0" or "+0" after the paranthesis, using the "show items with no data" option next to the variable and I have tried a different code: 

 

Remaining =

COALESCE(

   

CALCULATE(

        SUM('Table'[Column1]),

        FILTER(

            'Table',

            'Table'[Column 2] = "x1" ||

            'Table'[Column 2] = "x2" ||

            'Table'[Column 2] = "x3"

        )

    )

,

    0

)

 

Can anyone offer a change to help visualize all the data? 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Dehlilah,

In fact, this should be divided to two scenarios to handle your requirement.
If these records real include in your table, you can simply write a Dax expression and use if statement to check and replace them.
If these blanks case with table records missing, you may need to create a unconncted new table(as lbendlin said)with whole category values to use on matrix column field. Then you can extract the current table row and new table column value as condition to lookup records and return default value if this row/column value group does not include in your table.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Dehlilah,

In fact, this should be divided to two scenarios to handle your requirement.
If these records real include in your table, you can simply write a Dax expression and use if statement to check and replace them.
If these blanks case with table records missing, you may need to create a unconncted new table(as lbendlin said)with whole category values to use on matrix column field. Then you can extract the current table row and new table column value as condition to lookup records and return default value if this row/column value group does not include in your table.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

To report on things that are not there you need to use disconnected tables and/or crossjoins

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors