[MUSIC PLAYING]
Welcome to MSPTDA video number 24.
Yes, Microsoft Power Tools for Data Analysis.
Hey, in this video, we've got to talk about DAX data types.
And the fact that sometimes when we multiply and divide
the order of how we multiply or divide
gives us a different answer.
Look at this down here.
You're kidding me.
The same 1, 2, 3, numbers here equals 1,029.25.
But wait a second.
We're going to violate the communitive property
of multiplication in math.
We're simply going to change the order of the numbers,
and we get a different answer?
The fact comes from how we add data types
to each one of these numbers.
The data types in DAX, well, we can
have whole number, 0 decimals, currency or empower BI desktop,
it's called fixed decimal, up to four decimals and decimal data
type up to 15 decimals.
Here's the three numbers, we get 29.95.
But down here, we simply reverse the order, and we get 29.96.
That's because we had whole number, decimal,
and currency data types.
The interaction between those data types gave us 95 pennies.
But when we simply switched the order, currency,
decimal, whole number, we get 96 pennies.
Now, over here, we have some tables
that tell us how the left number with a certain data type,
and the right number with a certain data type
interact during the operation to yield a particular data type.
Now, I posted a question about this
at the Mr. Excel message board.
And Owen gave me an awesome answer about this and a link
to this amazing article by the great Marco Russo.
Now, for all of us that make calculations in Excel,
we know that we can multiply in any order
and the communitive property of multiplication holds.
We'll always get the same answer.
But the problem is in DAX.
When we're multiplying or dividing,
we have the potential to get different answers.
Adding and subtracting will not cause an issue.
So if we start on the left with a particular number and data
type, and the number on the right is a decimal,
we'll end up with a decimal.
But if we start with currency, and we're
going to multiply the right number as a decimal,
we're going to end up with currency.
The fact that during the first step,
meaning, multiply in the first two numbers,
can yield a particular data type which
then can have an interaction with the second data type,
that's where the issue comes.
So we want to look at this example right here.
We have an Excel table in an Excel worksheet.
Our data could just as easily be in Power BI Desktop.
I'm going to bring this into the data model
and build some DAX formulas to see exactly what's happening.
Click Add to the data model.
Now it opens up the PowerPivot for Excel window.
We have our three columns.
And if I click on W, notice in home formatting,
the data type is interpreted correctly.
Whole number, this one is decimal.
And notice for the currency column,
it automatically added decimal number.
If both of these are decimal numbers,
we wouldn't have a problem.
But what do we often do?
I'm going to change this to currency.
So I'm going to come up and change the data
type to a currency.
We also could have imported this from Power Query
with the same type of data types.
But over here, it is really polite.
Look at this, data might be lost.
This will affect how the data is stored and might
impact data precision.
I'm going to say, yes.
Now that is currency.
Now, what we want to do is just multiply whole number, decimal,
currency, and then we're going to do currency, decimal,
and whole number.
We're going to add an extra column.
We'll call it multiplying whole number and decimal which
will result in a decimal.
Enter up in the formula bar.
Click on the whole number times decimal.
And watch what happens when I hit Enter.
Up here data type, it says automatic.
But of course, internally, the DAX engine
converted whole number decimal for multiplication
to a decimal number.
Now that's a decimal number.
Second column, we'll do the previous which
is decimal and decimal times.
Currency will give us currency.
Up in the formula bar, previous times currency, all right.
So we have our numbers for our first WDC.
Now let's do CDW.
Currency times decimal will give us currency.
C times D and enter.
And sure enough, there's the auto currency.
Now we do our final column, previous times, whole number,
that will give us currency.
Previous times the whole number.
And when we hit Enter, there it is.
There's the auto currency.
And oh,oh, 96 pennies when we did it.
CDW, 95 pennies when we did it, WDC.
Now, I'm going to save this.
So how do you avoid discrepancies like this?
Well, you can pay attention to the data types on the left
and on the right.
However, it's probably just as easy
if you want to keep all the decimals to just not use
the currency.
In fact, watch this.
If I change this back to decimal,
then we have 95 and 95.
No problem.
Now, I'm going to Control Z. Now just like we
have to worry about when is the correct time
to round for any business calculation,
you also have to think when you're
using DAX about the data types.
All right, if you like that video,
be sure to click that thumbs up and leave a comment below
and tell me, what do you do in this situation?
Do you keep it all decimal?
Do you go all currency?
Or do you mix it?
All right, I'll see you in the comments.
And I'll see you next MSPTDA video.