Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
There is a calculated column that I can't understand. Here is the formula:
Solved! Go to Solution.
// Since the below is just
// syntax sugar
CALCULATE(
[Some Measure],
Table1[ID]
),
// it is transformed under
// the hood into
CALCULATE(
[Some Measure],
FILTER(
ALL( Table[ID] ),
Table1[ID]
)
)
// and since all non-0 integers
// are treated as TRUE()
// this formula IS VALID.
// However, because you've also
// got context transition from
// CALCULATE this filter overwrites
// what's coming from outside. But
// because all the other columns
// also are placed in the filter
// context what you see is a calculation
// for the row. But this is dangerous
// because if there are 2+ rows with
// exactly the same values but only
// different non-0 ID's, you'll get
// a wrong calculation result.
// Circular reference comes from the
// fact that context transition places
// ALL OTHER columns in the
// filter when context transition is
// executed. So, if you have 2 calc columns
// one will depend on the other. If the
// measures/conditions are intertwined
// you're out of luck.
// Since the below is just
// syntax sugar
CALCULATE(
[Some Measure],
Table1[ID]
),
// it is transformed under
// the hood into
CALCULATE(
[Some Measure],
FILTER(
ALL( Table[ID] ),
Table1[ID]
)
)
// and since all non-0 integers
// are treated as TRUE()
// this formula IS VALID.
// However, because you've also
// got context transition from
// CALCULATE this filter overwrites
// what's coming from outside. But
// because all the other columns
// also are placed in the filter
// context what you see is a calculation
// for the row. But this is dangerous
// because if there are 2+ rows with
// exactly the same values but only
// different non-0 ID's, you'll get
// a wrong calculation result.
// Circular reference comes from the
// fact that context transition places
// ALL OTHER columns in the
// filter when context transition is
// executed. So, if you have 2 calc columns
// one will depend on the other. If the
// measures/conditions are intertwined
// you're out of luck.
That's perfect. Thank you.
@kaisquared90 First of all this measure is incorrect, CALCULATE( [Some Measure], Table1[ID] ), either it should have a CALCULATE modifier such as VALUES, ALL, ALLSELECTED, ALLEXCEPT, other ALL functions or a single filter modifier such as KEEPFILTERS, or USERELATIONSHIP, CROSSFILTER. or a a predicate such as Table1[ID] = "Something"
The circular reference error is because you are trying to call CALCULATE in row context in a table that has duplicate values. is this the case?
when CALCULATE is used in row context, note that row context exists by default for a calculated column and we can manually create row context by using an iterator function, now when you use your measure CALCULATE ( [Some Measure], Table[ID] = "something" ) then CALCULATE converts the current row context ( all the values of all the column in the current row ) into a filter context, that now filters your [Some Measure] the first column works because for context transition CALCULATE is dependent on all the other columns of the table, now as soon as you create second column using CALCULATE, this column willl be dependent on the first calculated column that you have created, and the first column will be dependent on the second column so if in case you were allowed to create 2 columns then both would have depended upon each other which creates a loop, therefore you get a circular dependency error and aren't allowed to create another column.
However if you can bring a column in the table that has unique value for each row then you won't face this issue as the engine would depend on this column for creating other calculated columns. example, dimension table, in these table you can create same column using CALCULATE multiple times.
Another option is to use ALLEXCEPT, here is an example of using ALLEXCEPT on the fact table that has duplicate and without it on a dimension table
Hi Antriksh,
I had typed a reply to your answer and when I posted, the website gave an error and now the answer is lost!
In short, I thought the formula was wrong as well because the documentation describes the arguments required for CALCULATE the same as what you have said, but it is working in my report and giving real values so it's a bit mysterious to me, as well 🤔
Your explanation about the row context makes sense, though and explains why I get the circular reference error so many thanks for that. I actually found another solution (that someone else posted online):
@kaisquared90 < not very clear what are you trying to achieve here.
CALCULATE( [Some Measure], Table1[ID] =1 )
or
CALCULATE( [Some Measure], Table1[ID] in{1,2,3} )
In case you are you using ID in the slicer and it joined with the measure table it will filter. You do not need to do anything
if it is disconnected table
CALCULATE( [Some Measure],filter(Table1, Table1[ID] in values(Table1[ID] ) )
Hi Amit,
Yes you are describing how I understand the CALCULATE function to work, but the mystery is that the formula I gave works without error and gives the correct values (unless I try to use it again in another calculated column). Unfortunately, how the function is being used is still a mystery 🤔
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |