topic Re: In a matrix visual need to show 0 instead of blank from a measure and preserve filters in DAX Commands and Tips

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

akhaliq7 — Wed, 22 Nov 2023 09:02:23 GMT

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

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

Rupak_bi — Wed, 22 Nov 2023 09:21:09 GMT

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

)

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

akhaliq7 — Wed, 22 Nov 2023 09:32:00 GMT

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

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

Rupak_bi — Wed, 22 Nov 2023 09:54:06 GMT

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

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

akhaliq7 — Wed, 22 Nov 2023 10:04:43 GMT

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

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

akhaliq7 — Wed, 22 Nov 2023 10:54:18 GMT

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,

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

akhaliq7 — Wed, 22 Nov 2023 12:08:02 GMT

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

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

Anonymous — Fri, 24 Nov 2023 03:05:28 GMT

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.

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

akhaliq7 — Fri, 24 Nov 2023 10:02:08 GMT

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