A Puzzle or Bug? But interesting.

[Anonymous]

Hi @marcorusso and @AlbertoFerrari 

I consider both of you as the authority on DAX because I read your book on Mastering DAX. So I thought of highlighting something I came across; hoping that you could through some light on this.

Basic Maths

If we take any number N and divide it by another number D, we get a quotient Q and remainder R.

Properties

1. The quotient Q will be always less than or equal to N. (equal to when N is an integer and D = 1)

2. If we calculate - multiply the quotient Q with the divisor D and add the remainder R to it, we will get N. i.e. (Q * D + R = N)

Example:

N = 7, D = 2 gives Q = 3 and R = 1

3 * 2 + 1 = 7 (Q * D + R = N)

Now take a look at how it is in Power BI / DAX.

It turns out that, when the numbers are negative and give some remainder, then the QUOTIENT function and MODULUS function is not working properly. If you observe, -5 is greater than -5.2, how can the quotient be greater than the number itself? In this sample calculation, I have used 1 as the divisor. But any divisor can be used and the results are similar.

N = -5.2, D = 1 gives Q = -5 and R = 0.8

Now Q * D + R = -5 * 1 + 0.8 = -4.2 != -5.2

In this table, except the first field 'Number', all others are calculated columns using the following DAX.

Quotient = QUOTIENT(SomeNumbers[Number],1)Modulus = MOD(SomeNumbers[Number],1)Quotient * 1 + Remainder = SomeNumbers[Quotient] * 1 + SomeNumbers[Modulus]Results Check = SomeNumbers[Number] = SomeNumbers[Quotient * 1 + Remainder]

I wanted to validate if the logic of QUOTIENT function and MODULUS in DAX when dealing with negative numbers, so tried it in Excel, and in Excel also has a similar issue.

But to validate the logic mathematically, I tried to calculate the same thing in Python language. Python has an operator // (double slash) that gives the quotient and another operator % that gives the modulus. Here are the results.

Ignore the number of decimal places, but the results are accurate.

Case 1:

N = 5.2, D = 1 gives Q = 5 and R = 0.2 and

5 * 1 + 0.2 = 5.2 (Q*D + R = N)

Case 2:

N = -5.2 (negative), D = 1 gives Q = -6 (negative 6) and R = 0.80

Two things to oberve.

Q = -6 is less than the number N = -5.2 which is correct.

-6 * 1 + 0.80 = 5.2 (Q*D + R = N) this is alco correct.

I think there is something interesting going on with the QUOTIENT and MODULUS function in DAX and in Excel. 

[marcorusso]

As you noticed, DAX has the same behavior as Excel, because these mathematical functions share the same code. Even if this behavior is not correct, it doesn't matter. Because of compatibility issues with billions of existing (Excel) documents, the chances they will fix it are zero.

🙂