ZERO for measure returning no values

[Anonymous]

Dear Community

I have two measures and if the one does not return a value and the other one does, then the one that should be blank must return a ZERO/hyphen.

The problem is that my measure returns ZERO/hyphen for ALL values in the relevant dimension column in my visual, and not just the dimensions that have values.

Result required : I would like to filter out the dimension values that don't have a result in any of the measures in the visual. eg below, the red blocks should not be in the visual.

How can I amend my measure to achieve this? HASONEVALUE? ISINSCOPE? Or should I use a visual filter? Other solutions?

I would also like a solution, if possible, that does not require you to specify specific dimensions. Any dimension should be able to work with the solution.

Measure 1 and 2 - without ISBLANK
VAR _result =
CALCULATE (
[Measure],
FILTER (
'DIM_Types',
'DIM_Types'[Type] = "Refund"
)
)

RETURN
IF ( ISBLANK(_result), 0, _result )

Measure 1 - with ISBLANK
VAR _result =
CALCULATE (
[Measure],
FILTER (
'DIM_Types',
'DIM_Types'[Type] = "Refund"
)
)

RETURN
IF ( ISBLANK(_result), 0, _result )

[lbendlin]

 if the one does not return a value and the other one does, then the one that should be blank must return a ZERO/hyphen.

That is a design red flag.  Using a hyphen may be common practice in financial reports but it is anathema to Power BI.  Leave the spot blank.

(You will see hacks that say   measure = 0+calculation   but these are only masking your problem, they are not solving it)

[v-shex-msft]

Hi @Anonymous ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng