Return 0 % only when the outcome is actually 0, and blank otherwise

[mrclay82]

Hi,

I have a measure in DAX that I visualize by week that shows the performance in %:

Clear (%) =

VAR CL =

    CALCULATE (

        DISTINCTCOUNT ( 'Data - InboundDeliveryRows'[Row_ID] ),

        'Data - InboundDeliveryRows'[Clear] = 1,

        ‘Data - InboundDeliveryRows'[ID_GoodsReceiptArrivalDate] <> -1

            || ‘Data - InboundDeliveryRows'[ID_GoodsReceiptArrivalDate] <> -2,

        FILTER (

            ‘Data - InboundDeliveryRows',

            ‘Data - InboundDeliveryRows'[ordersortingnumber] = ‘Data - InboundDeliveryRows'[OrdernumberOrderRowNumberCount]

        )

    )

VAR TOT =

    CALCULATE (

        COUNT ( ‘Data - InboundDeliveryRows'[Row_ID] ),

        FILTER (

            ‘Data - InboundDeliveryRows',

            ‘Data - InboundDeliveryRows'[ordersortingnumber] = ‘Data - InboundDeliveryRows'[OrdernumberOrderRowNumberCount]

        )

    )

RETURN

    CL / TOT

This measure shows 0% (correctly) if the outcome is 0% but it also shows 0% if the outcome is blank. Could you please help me adjust this measure so that if the outcome is blank I want to show blank for that week and only 0% if the actual outcome is 0%.
Which means, if no InboundDeliveryRows for one week then show blank.

I would like to go from this:

To this (if the outcome is actually blank):

Many thanks!

[mrclay82]

Does anyone have any other suggestions? Much appreciated!

[Dangar332]

hi, @mrclay82 

try to change your return part as below

measure=
return
if(
 DISTINCTCOUNT ( 'Data - InboundDeliveryRows'[Row_ID] )=blank(),blank(),CL/TOT
)

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

[mrclay82]

Hi @Dangar332 ,

Thanks for your suggestion, I really thought that this was the solution!

Unfortunatelly the output is the same as the DIVIDE suggestion from @quantumudit , it shows blank if the output is blank but now it also shows blank when the output is 0%
Do you know if we can fix it?

Many thanks!

[v-shex-msft]

Hi @mrclay82 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[mrclay82]

Hi,

Thanks for your reply. It's a really complex data model so I don't really know how to make a dummy sample out of it =/

[quantumudit]

Well, you can use the following RETURN statement instead:

RETURN
    IF(ISBLANK(CL / TOT), BLANK, CL / TOT)

Please try this out and let me know if this solves your issue. Also one more advice is to use DIVIDE() instead of "/" symbol:

DIVIDE(CL, TOT)

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[mrclay82]

Hi @quantumudit,

Thanks for your quick reply!

The first solution gives me the following error message:
"syntax or semantic error at line 8, position 36 (this is where the RETURN statement is): The syntax for ',' is incorrect."


The second (DIVIDE) solution shows blank if the output is blank but now it also shows blank when the output is 0%
Do you have any suggestions here? Thanks