In a matrix visual need to show 0 instead of blank from a measure and preserve filters

[akhaliq7]

I would like to display the following:

Registered Products =

CALCULATE(

    SUM('Fact Orders'[Registered YN]),

    'Fact Orders'[Category ID] IN {10, 11, 12},

    'Date'[Year] = YEAR(TODAY())

)

inside a matrix visual, but for some categories it is displaying blank values this is because I am showing more than 1 measure in values of the matrix visual.

What I tried

Using if statement, adding 0 to sum but when I do this more than 1 category shows

[Anonymous · Accepted solution]

Hi  ,

I have created a simple sample, please refer to.

Create a table includes Year Fields. 

Table 2 = VALUES('Table'[Year])

Then create a measure.

Measure =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            'Table',
            [Portfolio] = "A"
                && [Year] = SELECTEDVALUE ( 'Table 2'[Year] )
        )
    )
VAR _b =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            'Table',
            [Portfolio] = "B"
                && [Year] = SELECTEDVALUE ( 'Table 2'[Year] )
        )
    )
VAR result =
    CALCULATE (
        MAX ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Portfolio]
                IN VALUES ( 'Table'[Portfolio] )
                    && 'Table'[Year] IN VALUES ( 'Table 2'[Year] )
        )
    )
VAR _answer_ =
    IF ( result <> BLANK (), result, 0 )
RETURN
    IF ( ISINSCOPE ( 'Table'[Portfolio] ), _answer_, _b - _a )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[akhaliq7]

I need to try this out but do not have the time right now but have accepted the solution thanks.

[akhaliq7]

If I am not mistaken this issue may relate to the evaluation context and it is possibly applying the filter first then giving 0 value to all the other categories and adding them in the matrix visual, but not sure how to fix this

[Rupak_bi]

then try to put If statement before calculate. else share sample data.

[akhaliq7]

I have tried to create a sample dataset but keep getting different errors when displaying the data, and cannot share the original file as well, I may leave the blanks as it is unless someone else can solve this issue,

[akhaliq7]

I tried putting the if statement before calculate but that didn't work i'll share a sample file after this reply

VAR Result = Calculate code
RETURN IF( ISBLANK(Result), 0, Result)

But this ignored the filter applied in calculate

[Rupak_bi]

try this,

Registered Products =

CALCULATE(

    if(SUM('Fact Orders'[Registered YN]>0,SUM('Fact Orders'[Registered YN]),0),

    'Fact Orders'[Category ID] IN {10, 11, 12},

    'Date'[Year] = YEAR(TODAY())

)

[akhaliq7]

The above ignores the category id filter which I need to preserve