Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Is there an easy way to manage NaN or Infinity ?
I tried to replace error, no impact.
I tried to replace "NaN" by 0, no impact (when it's working with Null).
In my formula I tried with "try a/b otherwise 0" but I still have the error.
I saw some test based on Number.IsNaN but it just tests NaN... what about Infinity or other errors...
The only way I found is to test Numerator and denominator (if Numerator=0 or Denominator = 0 then 0 else .... ), but maybe there is a better methodology like an equivalent to IFERROR in Excel?
Thank you for your help.
(I'm using PowerQuery in Excel, so I don't have DAX measures & operators).
Solved! Go to Solution.
Thank you @edhans for your answer.
So there is no native function in powerquery to valuate those kind of error... that's sad.
Thanks for the article, I'll give a try later.
By waiting I keep my methodology tant consits in checking if Numerator & denominator are not 0 before doing a division... not the smartest but at least it works.
You can convert numbers to text and evaluate them as a string with NaN or ∞
if Number.ToText([number]) = "∞"
or Number.ToText([number]) = "NaN"
then 0 else [number]
You will need to use the Number.IsNaN, Number.PositiveInfinity, Number.NegativeInfiinity, etc. The try/otherwise construct often doesn't work with these because they are not errors - even though Excel would trap them with IFERROR().
I recommend this excellent article on this issue which goes through all of these and more functions, and includes a very helpful function at the bottom to check all of these at once.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you @edhans for your answer.
So there is no native function in powerquery to valuate those kind of error... that's sad.
Thanks for the article, I'll give a try later.
By waiting I keep my methodology tant consits in checking if Numerator & denominator are not 0 before doing a division... not the smartest but at least it works.
Well @LaurentZ - it isn't technically an error, it is a valid result. But I do agree for most use cases NaN or any Infinity numbers aren't desired. The custom function I linked to will test for 4-5 results that are valid, but typically not desired, and remove them.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting