Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

Снимок.PNG

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
Comments
Greg_Deckler
Super User

Excel's MOD function works identically but 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.

image.png

Greg_Deckler
Super User

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.

 

image.png

Greg_Deckler
Super User

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....

az38
Community Champion

@Greg_Deckler 

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

It should be mentioned as remark in documentation

Greg_Deckler
Super User

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

az38
Community Champion

@Greg_Deckler 

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

Greg_Deckler
Super User

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