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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors