Return 0 if measure card is showing empty

[harshagraj]

Hello all please see the below calculation and applied on a measure with some filters on visuals externally.
I am not able to achieve 0 when the card is empty. I tried Coalesce and also added +0

PF Flag Running =

var _a = CALCULATE(

    COUNTA('Dcms_daily_SAP_dataload_V'[Premium_Freight_Flag]),

    FILTER(

        CALCULATETABLE(

            SUMMARIZE(

                'Dcms_daily_SAP_dataload_V',

                'Dcms_daily_SAP_dataload_V'[Week_Num],

                'Dcms_daily_SAP_dataload_V'[Week_Name]

            ),

            ALLSELECTED('Dcms_daily_SAP_dataload_V')

        ),

        ISONORAFTER(

            'Dcms_daily_SAP_dataload_V'[Week_Num], MAX('Dcms_daily_SAP_dataload_V'[Week_Num]), DESC,

            'Dcms_daily_SAP_dataload_V'[Week_Name], MAX('Dcms_daily_SAP_dataload_V'[Week_Name]), DESC

        )

    )

)

Return

IF(

    CALCULATE(_a)=BLANK(),

    0,

    _a)
@amitchandak @Idrissshatila @Greg_Deckler @Sahir_Maharaj 

[Sahir_Maharaj]

Hi @harshagraj ,

Try using the IFNULL or IFERROR function to return 0 when the calculation returns a blank or error:

PF Flag Running = var _a = CALCULATE( COUNTA('Dcms_daily_SAP_dataload_V'[Premium_Freight_Flag]), FILTER( CALCULATETABLE( SUMMARIZE( 'Dcms_daily_SAP_dataload_V', 'Dcms_daily_SAP_dataload_V'[Week_Num], 'Dcms_daily_SAP_dataload_V'[Week_Name] ), ALLSELECTED('Dcms_daily_SAP_dataload_V') ), ISONORAFTER( 'Dcms_daily_SAP_dataload_V'[Week_Num], MAX('Dcms_daily_SAP_dataload_V'[Week_Num]), DESC, 'Dcms_daily_SAP_dataload_V'[Week_Name], MAX('Dcms_daily_SAP_dataload_V'[Week_Name]), DESC ) ) ) Return IFNULL(_a,0)

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

[harshagraj]

Hi @Sahir_Maharaj IFNULL doesnt support.Thank you

[Greg_Deckler]

@harshagraj Try:

Return
 _a + 0

[harshagraj]

HI @Greg_Deckler thanks for the responce but it is not working.

[Greg_Deckler]

@harshagraj Well, if it was me, I would break that formual into multiple variables in order to debug what is going on internally in that formula. Better yet, I would drop the use of CALCULATE and CALCULATETABLE because they do not appear to be necessary at all. For example, you could use COUNTAX instead of COUNTA. You could use FILTER(SUMMARIZE(ALLSELECTED(...),...),...) for example versus your current construct. And I wouldn't use ISONORAFTER but rather something like >=. That's me though, not sure why you aren't getting a value there in your card visual. For example, something like:

Measure = BLANK() + 0

This will always return 0 in a Card visual. So if your VAR _a is returning blank, then you should end up with a 0. So, something else is going on here but not enough information to know what that is at the moment.