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?
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.
Here is something interesting, in the documentation for the Excel MOD function:
and DAX documentation on MOD:
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!!!
@az38 - Here is some more information, I am not sure if these apply but they seem related potentially:
well, it looks like MOD() is only valid for integer values
It should be mentioned as remark in documentation
@az38 - Would you mind if I posted a "Better MOD" to the Quick Measures gallery and give you credit?
no problem, it is your solution and you are the King of Quick Measures gallery 🙂
@az38 - Thanks! And done. To **bleep** with MOD!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.