topic Re: Return 0 % only when the outcome is actually 0, and blank otherwise in DAX Commands and Tips

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

mrclay82 — Mon, 15 Apr 2024 05:21:44 GMT

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!

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

quantumudit — Mon, 15 Apr 2024 07:24:07 GMT

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

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

mrclay82 — Wed, 17 Apr 2024 08:25:26 GMT

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

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

Anonymous — Thu, 25 Apr 2024 06:27:33 GMT

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

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

mrclay82 — Thu, 25 Apr 2024 06:40:23 GMT

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 =/

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

Dangar332 — Thu, 25 Apr 2024 06:59:07 GMT

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.

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

mrclay82 — Fri, 03 May 2024 09:35:50 GMT

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!

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

mrclay82 — Tue, 14 May 2024 11:45:06 GMT

Does anyone have any other suggestions? Much appreciated!