Ciao friends, in this video,
I want to show you how to first understand
and then solve a problem that you are likely to face
if you write perfectly legit code in Power BI,
but in the code you are changing the filter context
over a column that happens to be sorted by another column.
We will first look at the problem,
then we will need to understand it,
that requires some digging so we will need
to investigate on it, look at the queries generated
by Power BI, in order to understand exactly
what originates the problem.
Once the scenario is clear,
fixing it is really simple, but the real interesting part
is understanding where the problem comes from.
Sounds interesting?
Follow me.
The first step is understanding the scenario
and looking at the problem.
In this report, I have a slicer by year,
and a matrix that is slicing by month name
and showing the sales amount.
Now I want to compute the percentage of the sales
in one month against the grand total of the sales.
That requires computing a numerator and a denominator,
the numerator is just the sales amount
and the denominator need to be the grand total.
Now I have already written the code,
and you see that I'm doing DIVIDE,
sales amount that is this number in the filter context
of each individual cell and at the denominator,
I'm using CALCULATE again sales amount
but using ALL date month name.
What does ALL do?
ALL removes the filter on the month name,
and it keeps the filter on the year.
So by doing that, it retrieves the grand total
and the result formatted as a percentage
should be the correct value.
Unfortunately, if I use this measure and I drop it in
my report, it shows a hundred percent which is just wrong.
Beside, it's interesting to note
that the formula by itself, works just fine.
If I look at the same report in Excel,
this is an Excel file connected with the same Power BI file.
And I'm using the same month names
the same filter, the same values,
and most important here I have the same measure.
It is working just fine.
It is computing the correct percentage.
So why is it that I have a measure that works in Excel
and it does not work in the latest version
of the DAX engine in Power BI?
That is preposterous!
So we need to understand exactly where the problem is.
In order to do that, we will first need
to focus on where the problem is.
Remember it works in Excel, it does not work in Power BI.
So the problem is not in the DAX code
but it is likely that we are not taking
into account the specific way Power BI
runs the query against my model.
To understand that, we will look
at exactly the query generated by Power BI
to retrieve information that populate my matrix.
And there, we will find the problem.
Let's do that together.
The first thing that we need to do is grab
the query generated by Power BI
in order to populate our matrix.
So to do that, we go to view,
enable the performance analyzer, we start recording
and then we refresh the visuals.
We have our matrix.
I can now copy the query and paste it in DAX Studio.
So I already have DAX Studio open
and this is the query generated by Power BI.
I never look at queries as they are generated by Power BI.
I always do some makeup in order to make them
easier to read.
So let's do that together.
We have this filter that I prefer to see there.
Then we can get rid of the sub totals, because I'm not
interested in looking at individual subtotals,
the problem is in the cells of the matrix
not in the subtotal.
And I also have this TOPN, all the queries
in Power BI always have a TOPN
but I'm not interested in looking at that.
I just want to evaluate the original query.
And finally, I have removed the subtotal column.
So I also need to get rid of it.
Now I can format my code and start to reason on top of it.
First of all, let's see the result.
The result contains the month name, the month number,
the sales amount and the percentage over the month
which is always one, that is a hundred percent.
Now, if you remember the matrix, let's look at it again,
getting rid of the performance analyzer.
We have the month name, the sales amount,
and the percentage over the month.
Whereas in the same result returned
by the query in DAX Studio,
we have the month name, the sales amount,
the percentage but we also have the month number
which is explicitly requested here
because SUMMARIZECOLUMNS is grouping
by month name and month number.
The reason for this is because in the ORDER BY
it requires to order by month number and month name.
And this is the same order that will be kept in the matrix.
You can see it here.
It is sorting January, February, March
and not using alphabetical sorting.
It is doing that because,
the month name is sorted by month number.
We can look at that if we search for the month name,
add the sort by column, you see that month name
is sorted by month number.
Now, whenever you have a column that is sorted
by another column, the query generated
by Power BI contains not only the column you're querying
but also the column that is needed to do the order by,
and this creates a problem in the code.
Let's understand it a bit better.
When we run this query,
we evaluate the percentage over month.
It is in sales, let me add it to the report.
We get rid of it, but I want to see the code
of percentage over month, which is DIVIDE sales amount
by CALCULATE sales amount, ALL date month.
Now, this ALL gets rid of the filter on the month name,
which is present in the filter context
because SUMMARIZECOLUMNS is grouping by month name,
but SUMMARIZECOLUMNS is also grouping by month number.
And we are not removing that filter.
So the final filter context that computes
the denominator will not have a filter on the month name,
but will still have a filter on the month number.
And because there is a one-to-one relationship
between the values and the two columns,
removing the filter only from the month name
does not change the actual number computed by sales amount.
To fix it, what we need to do is get rid of the filter
from the month name and from the month number.
So we need to add date, month number, to the ALL statement
in order to get rid of both filters.
If I run the query now, you see that the percentage
is now 7%, 7%, 8%.
So, by removing the filter from both columns,
now I have a code that works fine.
This is fine, I now have my measure.
Let me copy.
Go back to Power BI, and change the code here.
I put it in place, hit Enter,
and suddenly my measure is working just fine.
Now it is computing the correct percentage.
This happens every time you have a column that is sorted
by another column; it happens in Power BI
and not in Excel because Excel uses MDX
and MDX is not subjected to the same behavior.
MDX uses different techniques that do not
require requesting also the month number,
if you want to sort the month name by month number.
Now, scenarios like this are very simple to solve
once you understand the problem.
For newbies or people who are not used
to this peculiar behavior of Power BI, it might seem strange
because the code is just fine, but it's the way the query
is executed that creates the problem.
The thing is, once you get used to debug the things
the right way, Power BI holds no secret.
You can always look at the code
using the performance analyzer
and understand exactly what is happening.
Enjoy DAX.