cancel
Showing results for
Did you mean:

## MOD DAX function strange behaviour

Hi

what's wrong with MOD() function?

When I was checking my solution to this thread https://community.powerbi.com/t5/Desktop/1st-Decimal-Number-needs-to-be-Even/m-p/1081369#M500728 I noticed a very strange behaviour of this function

There is no visual aggregation.

Why the result for even decimal numbers is 0.2, not 0 ?

Second point, according https://dax.guide/mod/ and https://docs.microsoft.com/en-us/dax/mod-function-dax it should return me INTEGER and not decimal at all.

I feel the bug is somewhere, but where is it? In my head, in DAX, in Power BI DAX engine or in the documentation?

Status: New
Super User IV

The behavior you are seeing is exactly the behavior of Excel's MOD function. Wolfram Alpha returns 0 (see below). So, there is something wrong with Excel and DAX's MOD function with decimal numbers.

My understanding of modulus is that it is supposed to return the remainder. As you point out, there should be no remainder dividing 1.2 by .2.

Super User IV

Here is something interesting, in the documentation for the Excel MOD function:

https://support.office.com/en-us/article/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3

and DAX documentation on MOD:

https://docs.microsoft.com/en-us/dax/mod-function-dax

They provide the alternative formula for MOD:

The MOD function can be expressed in terms of the INT function: MOD(n, d) = n - d*INT(n/d)

If you use that formula, you get the correct results, 0 for even numbers like 1.2!!!

So....

Super User IV

@az38 - Here is some more information, I am not sure if these apply but they seem related potentially:

Super User II

well, it looks like MOD() is only valid for integer values

It should be mentioned as remark in documentation

Super User IV

@az38 - Would you mind if I posted a "Better MOD" to the Quick Measures gallery and give you credit?

Super User II

no problem, it is your solution and you are the King of Quick Measures gallery 🙂

Super User IV

@az38 - Thanks! And done. To **bleep** with MOD!

Completed Ideas