topic Re: Displaying "0" in table or matrix in DAX Commands and Tips

Displaying "0" in table or matrix

Dehlilah — Tue, 20 Feb 2024 14:18:29 GMT

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),

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"

)

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

Re: Displaying "0" in table or matrix

lbendlin — Tue, 20 Feb 2024 23:08:40 GMT

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

Re: Displaying "0" in table or matrix

Anonymous — Wed, 21 Feb 2024 05:41:33 GMT

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