Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: A Puzzle or Bug? But interesting.

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

A Puzzle or Bug? But interesting.

05-08-2021
01:16 AM

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.

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-08-2021
01:38 AM

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.

🙂

Announcements

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

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

Featured Topics

Top Kudoed Authors

User | Count |
---|---|

359 | |

223 | |

93 | |

67 | |

54 |