Replacing Blank Values with 0's (without adding new rows)

[hduck123]

Hello,

I have some messy that returns the correct desired result, but I am having trouble replacing the blank values with 0's without adding new rows to the table.

There are two dimensions: Accounts and Items. Each item is associated with an account, but can have transactions on the fact tables at accounts it is not associated with. The two fact tables are cycles and processes. Both fact tables are related to both dimensions. 

When trying the IF(ISBLANK( pattern, additional rows are added to the table that do not exist in either of the transaction rows, which makes sense. Below is a screenshot of the data. The table on the bottom left is the correct result (using simple counts) and the table on the right is incorrect using the IF(ISBLANK( pattern.  

How can this be achieved in DAX or through the UI?

[v-jingzhang]

Hi @hduck123 

It seems that you need to remove the rows with both count values are 0. So you can try adding measures like below and use them instead in the table visual.

Count - Cycles =
IF (
    [IfIsBlank - Cycles] = 0 && [IfIsBlank - Processes] = 0,
    BLANK (),
    [IfIsBlank - Cycles]
)


Count - Processes =
IF (
    [IfIsBlank - Cycles] = 0 && [IfIsBlank - Processes] = 0,
    BLANK (),
    [IfIsBlank - Processes]
)

Best Regards,

Community Support Team _ Jing Zhang

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

[MattAllington · Accepted solution]

I suggest you create an auxiliary measure that adds the 2 numbers together. This new measure can be placed in the visual-level filters in the filter pane. Set it to >0