Measure behaving strange - returning 0 for some fields and blanks for others

[duncip]

Hey,

For the tables in our dashboards we try and show 0s instead of blanks so it looks neater and it's not hiding any columns. Most of the time, this works easily enough by using the +0 functionality in a measure. However, I've now created a measure (it's based on a SQL view) and it seems to be doing it correctly for some of the columns/rows but not others:

You can see in the second row, first column it just returns a blank and I'm not sure why? I've tried doing the measure in 2 different ways and both are resulting in the same:

All Jobs = 
    VAR _MeasureCalc = CALCULATE(
        DISTINCTCOUNT(vw_table[ORDER_NUMBER]),
        FILTER(
            vw_table,
            vw_table[Status_Categorisation] = "New"
    )
    ) +0
    
    RETURN
    IF(NOT(ISBLANK(_MeasureCalc)),_MeasureCalc, 0)

and the more standard one we use:

All Jobs Test = 
    CALCULATE(
        DISTINCTCOUNT(vw_table[ORDER_NUMBER]),
        FILTER(
            vw_table,
            vw_table[Status_Categorisation] = "Dispatched" &&
            vw_table[Priority_Categorisation] <> "No-Category" &&
            not(isblank(vw_table[Priority_Categorisation]))
    )
    ) +0

Has anyone come across this before? Thanks!

[ThxAlot]

Based on your description, it's inferred the column and row of matrix are both from vw_table; thus no need to waste time seeking method to make any value appear in cell (IOW, P1). Power BI alreay implemented an intelligent mechanism call "Auto-Exist". Google this term by yourselft; there are tons of references out there.

Btw, from syntax piont of view, simplify your measure,

All Jobs Test =
CALCULATE(
    DISTINCTCOUNT( vw_table[ORDER_NUMBER] ),
    vw_table[Status_Categorisation] = "Dispatched",
    vw_table[Priority_Categorisation] <> "No-Category",
    NOT ( ISBLANK( vw_table[Priority_Categorisation] ) )
) + 0

[bhanu_gautam · Accepted solution]

@duncip , Try updating your measure using COALESCE

All Jobs =

VAR _MeasureCalc =

    CALCULATE(

        DISTINCTCOUNT(vw_table[ORDER_NUMBER]),

        FILTER(

            vw_table,

            vw_table[Status_Categorisation] = "New"

        )

    )

RETURN

COALESCE(_MeasureCalc, 0)

And

All Jobs Test =

VAR _MeasureCalc =

    CALCULATE(

        DISTINCTCOUNT(vw_table[ORDER_NUMBER]),

        FILTER(

            vw_table,

            vw_table[Status_Categorisation] = "Dispatched" &&

            vw_table[Priority_Categorisation] <> "No-Category" &&

            NOT(ISBLANK(vw_table[Priority_Categorisation]))

        )

    )

RETURN

COALESCE(_MeasureCalc, 0)

[Dangar332]

Hi, @duncip 

In your all jobs Measure elemenate IF() part just simply use +0 after your calculation.
like below

All Jobs = 
CALCULATE(
        DISTINCTCOUNT(vw_table[ORDER_NUMBER]),
        FILTER(
            vw_table,
            vw_table[Status_Categorisation] = "New"
    )
    ) +0
    
   

Best regards,
Dangar 

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