topic Re: Measure return Inf in DAX Commands and Tips

Measure return Inf

NickzNickz — Wed, 03 May 2023 08:04:04 GMT

Hi ...

I have below measure:

Variance Projection = VAR _Nos = [Sum Achievement %] - [Sum Projection] VAR _percentage = _Nos / [Sum Projection] VAR _direction = SWITCH( TRUE(), [Sum Achievement %] >= [Sum Projection], UNICHAR(9650), [Sum Achievement %] < [Sum Projection], UNICHAR(9660) ) Return _direction & " " & FORMAT(_percentage, "0.00%") & " (" &FORMAT(_Nos, "0.00") &")"

If the Sum Projection has a value the result return as expected.

However, if empty/0 its returns Inf ... How can I resolve that?

At least the result return value of 0.00 instead of Inf.

Thank you.

Regards,

NickzNickz

Re: Measure return Inf

ValtteriN — Wed, 03 May 2023 08:19:03 GMT

Hi,

Instead of using "/" use DIVIDE function. This will avoid any issued with dividing by 0.

E.g. DIVIDE(_Nos, [sum projection])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Measure return Inf

johnt75 — Wed, 03 May 2023 08:25:32 GMT

You can use COALESCE to return 0 instead of blank,

Variance Projection = VAR _Achievement = [Sum Achievement %] VAR _Projection = [Sum Projection] VAR _Nos = _Achievement - _Projection VAR _percentage = COALESCE ( DIVIDE ( _Nos, _Projection ), 0 ) VAR _direction = SWITCH ( TRUE (), _Achievement >= _Projection, UNICHAR ( 9650 ), _Achievement < _Projection, UNICHAR ( 9660 ) ) RETURN _direction & " " & FORMAT ( _percentage, "0.00%" ) & " (" & FORMAT ( _Nos, "0.00" ) & ")"

Re: Measure return Inf

NickzNickz — Mon, 08 May 2023 07:13:00 GMT

It's working as expected. Thank you so much....

Re: Measure return Inf

NickzNickz — Mon, 08 May 2023 07:14:43 GMT

Thank you so much.