I hope this question can be answered in theory but I also have attached an example PBI file of the incorrect results that I am getting. Mock Data PBI.pbix (1.6 MB)
Why do we need to separate some larger measures into smaller measures in order to get correct results? I have had a response this is query previously (https://community.powerbi.com/t5/Desktop/Measure-branching-Vs-variables-in-a-larger-measure/m-p/2976188#M1017301) saying that completed measures get wrapped in a calculate and that’s the difference between combining measures into 1 larger measure and splitting them into smaller ones. Attempting to solve this, I tried to wrap variables within my larger measure in calculate but still have the same issue.
For example, one larger measure can be:
LargeMeasure =
Variable A = 1
Variable B = A + 2
Variable C = B + 3
RETURN C
This over simplified example measure returns incorrect results but If I split it into smaller measures, the results are correct, for example:
SmallMeasure1 =
Variable A = 1
RETURN A
SmallMeasure2 =
Variable B = SmallMeasure1 + 2
RETURN B
SmallMeasure3 =
Variable C = SmallMeasure2 + 3
RETURN C
If I then use small measure3 in a table, it returns correct results, but LargeMeasure’s results would be incorrect. Why is that?
My sample file attached sheet “daily budget” contains tables where the incorrect results are displayed:
“3 Daily budget build” is the correct result but “daily budget large measure incorrect” is the incorrect result. Both measures use the same DAX to get to the result but the “3 daily budget build” is split into 3 parts, being
1 daily budget build
2 daily budget build
3 daily budget build
Hi Timmay,
Hope my feedback finds you helpful.
What I have understood by seeing your queries is at what level you are using measure branching.
So if I oversimplify it in LargeMeasure when you return C in LargeMeasure you have a value from Variable A to B and the independent C value is also there. That is why you are facing an extra amount in LargeMeasure.
on the other hand in SmallMeasure when you are returning variable C you are just adding the number of only SmallMeasure2 and the independent value.
So in LargeMeasure you are summing from the start of the measure and in SmallMeasure you are just summing the number from the previous step.
Firstly, let’s understand why you’re getting the wrong results in your large measure and then we’ll see how we can convert those 3 measures into one consolidated measure to achieve the same results.
So in your large measure, the first mistake which you had made is referencing a naked variable inside a “CALCULATE()” function. You cannot use/reference/call a naked variable directly inside CALCULATE since variables are considered as “constants”. That is, they evaluate the results and those results are stored or freezed and therefore, when called directly inside CALCULATE, it’ll just evaluate it over the already freezed results. Below is the link provided from RADACAD website which will explain it in-detail about how and when to make right usage of variables.
Note: Before proceeding further with the later part of the explanation, please go through the link so that next part of explanation will be more understandable.
Now, once you’ve gone through the above provided link. Here’s how your measure was evaluating the results at the back-end.
Create a physical DAX generated table by pasting the below provided formula. -
Incorrect Budgets Table =
VAR _vTable_Budgets =
ADDCOLUMNS(
SUMMARIZE(
'Budget' ,
'Budget'[Budget Date] ,
'Budget'[Month End Date] ) ,
"@DailySales",
DIVIDE(
[Budget Sales],
DATEDIFF( 'Budget'[Budget Date] , 'Budget'[Month End Date] , DAY ) + 1 ) )
VAR _Cross_Join =
CROSSJOIN(
VALUES( Dates[Date] ) ,
_vTable_Budgets )
RETURN
_Cross_Join
You’ll see that, it’ll generate all possible combination of rows between “Dates[Date]” and “Budgets[Budget Date] & Budgets[Month End Date]” columns. See this -
And when you convert the above provided formula into a measure, it’ll yield the same wrong results which you were getting originally. Here’s the converted DAX measure based on above provided formula -
It means that, when you used/referenced/called a naked variable directly inside CALCULATE, it didn’t do anything in terms of evalaution with regards to that aspect. But on top of that, the other functions which you used inside that large measure just expanded the rows to all possible combination of dates. And to add to that misery, “SUMX()” function summed-up the results of those possible combination of rows for that particular financial year.
So now, while fixing the measure we need to ensure that only row of date for each day is generated inside a date range of budget for that particular month. For example, if budget month is from “1st July, 2022 - 31st July, 2022”, it needs to generate only 31 rows of dates for that given date range. And then against those generated set of dates, budget figure for each day needs to be evaluated accordingly. Below is the consolidated DAX measure alongwith the screenshot of the final results provided for the reference -
I’m also attaching the working of the PBIX file as well as an Excel file which contains the numbers evaluated based on wrong measure at the back-end of the engine for cross-verification for your reference purpose.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks for the solution, I’ve been away on holidays. I’ve marked your reply as a solution as I’m sure it’s correct (your solutions always are). Any questions, i’ll let you know when I get back and revise.
Thanks Harsh I’ve had a look through, a very comprehensive solution and works well.
Looks like I better be a bit more careful using variables from now on.
Will spend a bit of time getting to know how the crossjoin function works, haven’t used it too much up until now.
Interesting that my original code also works if you redo it with the variables taken out. But unfortunately, this turns the code into an unreadable mess
VAR AddDates =
// Put the amounts into a table with each day
ADDCOLUMNS(
VALUES(Dates[Date]),
"Amount",
CALCULATE(
// Returns the daily amount for each day in the date table relating to the relevant month
CALCULATE(
SUMX(
// To calculate the daily budget by taking the total sales amount and dividing by the days in the month
ADDCOLUMNS(
SUMMARIZE(
'Budget',
'Budget'[Date],
'Budget'[Month End Date]
),
"@DailySales",
DIVIDE(
[Budget Sales],
DATEDIFF(
'Budget'[Date],
'Budget'[Month End Date],
Day
) + 1
)
),
[@DailySales]
),
FILTER(
VALUES( 'Budget'[Date] ),
'Budget'[Date] <= MAX( Dates[Date] )
),
FILTER(
VALUES( 'Budget'[Month End Date] ),
'Budget'[Month End Date] >= MIN( Dates[Date] )
)
)
)
)
VAR SumFinal =
// SumX on the table to return a scalar value
SUMX(
AddDates,
[Amount]
)
RETURN
// Final result
SumFinal
![Combination of Rows Between Dates[Date] column and Budgets[Budget Date] & Budgets[Month End Date] columns](https://forum.enterprisedna.co/uploads/db7134/original/3X/5/b/5bcca816bf30486f8b595254a959387bb9c4a22a.png)
