Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
harshagraj
Post Partisan
Post Partisan

Return 0 if measure card is showing empty

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,
5 REPLIES 5
Sahir_Maharaj
Super User
Super User

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" 🙂

 


Did I answer your question? Mark my post as a solution, this will help others!

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

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Hi @Sahir_Maharaj IFNULL doesnt support.Thank you

Greg_Deckler
Super User
Super User

@harshagraj Try:

Return
 _a + 0

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

harshagraj_0-1675321507362.png

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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.