topic Receiving result NaN from DAX measure in DAX Commands and Tips

Receiving result NaN from DAX measure

LYBridges — Wed, 13 Nov 2024 12:48:23 GMT

It looks like it's due to using "/" vs DIVIDE, but I'm not sure how to fix the DAX to allow for 0's in the calculations. Any suggestions?

OverallReady =
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Group Structure Config"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), 'Append1'[TotalVolExpected])
) +
(
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Provider Demographics"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolExpected])
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Network"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolExpected])
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Prior Authorization"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolExpected])
)
) / 3
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "New Membership"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolExpected])
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Product Builds"),
'Append1'[TotalVolConfigured] / SUMX(FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolExpected])
) +
AVERAGEX(
FILTER('Inputs - Claims Ready', 'Inputs - Claims Ready'[Layer] = 3),
'Inputs - Claims Ready'[Overall % Complete]
)
) / 5

Re: Receiving result NaN from DAX measure

bhanu_gautam — Wed, 13 Nov 2024 13:39:21 GMT

LYBridges , Try using

OverallReady =
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Group Structure Config"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), 'Append1'[TotalVolExpected]), 0)
) +
(
(
SUMX(
FILTER('Append1', 'Append1'[Category] = "Provider Demographics"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolExpected]), 0)
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Network"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolExpected]), 0)
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Prior Authorization"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolExpected]), 0)
)
) / 3
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "New Membership"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolExpected]), 0)
) +
SUMX(
FILTER('Append1', 'Append1'[Category] = "Product Builds"),
DIVIDE('Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolExpected]), 0)
) +
AVERAGEX(
FILTER('Inputs - Claims Ready', 'Inputs - Claims Ready'[Layer] = 3),
'Inputs - Claims Ready'[Overall % Complete]
)
) / 5

Re: Receiving result NaN from DAX measure

Poojara_D12 — Thu, 14 Nov 2024 06:18:28 GMT

Hi LYBridges

To handle division by zero issues in your DAX calculation, you can replace the / operator with the DIVIDE function. The DIVIDE function has an optional third parameter that allows you to specify an alternative result if the denominator is zero. This prevents errors when TotalVolExpected is zero.

Here's how to rewrite your OverallReady measure using DIVIDE instead of /:

OverallReady = ( SUMX( FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), DIVIDE( 'Append1'[TotalVolConfigured], SUMX(FILTER('Append1', 'Append1'[Category] = "Group Structure Config"), 'Append1'[TotalVolExpected]), 0 ) ) + ( DIVIDE( SUMX( FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolConfigured] ), SUMX(FILTER('Append1', 'Append1'[Category] = "Provider Demographics"), 'Append1'[TotalVolExpected]), 0 ) + DIVIDE( SUMX( FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolConfigured] ), SUMX(FILTER('Append1', 'Append1'[Category] = "Network"), 'Append1'[TotalVolExpected]), 0 ) + DIVIDE( SUMX( FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolConfigured] ), SUMX(FILTER('Append1', 'Append1'[Category] = "Prior Authorization"), 'Append1'[TotalVolExpected]), 0 ) ) / 3 + DIVIDE( SUMX( FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolConfigured] ), SUMX(FILTER('Append1', 'Append1'[Category] = "New Membership"), 'Append1'[TotalVolExpected]), 0 ) + DIVIDE( SUMX( FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolConfigured] ), SUMX(FILTER('Append1', 'Append1'[Category] = "Product Builds"), 'Append1'[TotalVolExpected]), 0 ) + AVERAGEX( FILTER('Inputs - Claims Ready', 'Inputs - Claims Ready'[Layer] = 3), 'Inputs - Claims Ready'[Overall % Complete] ) ) / 5

Explanation

Each division operation now uses DIVIDE(numerator, denominator, 0), which will return 0 if the denominator is 0, avoiding any divide-by-zero errors.
The optional third parameter in DIVIDE (here set to 0) specifies what should be returned when the denominator is 0. You can adjust this to another default value if desired.

This approach should solve the problem and prevent errors due to zero values in the denominator. Let me know if this works or if you need further adjustments!

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,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Receiving result NaN from DAX measure

LYBridges — Fri, 15 Nov 2024 13:40:07 GMT

Thanks so much! That solved the error!