cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sreenathv
Super User
Super User

A Puzzle or Bug? But interesting.

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

sreenathv_0-1620460177140.png

 

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.

 

sreenathv_1-1620460804019.png

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. 

1 REPLY 1

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.

🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.