CALCULATE Filter argument is just a column

[kaisquared90]

There is a calculated column that I can't understand. Here is the formula:

CALCULATE( [Some Measure], Table1[ID] )

It does what I want i.e. calculates [Some Measure] aggregated by the unique [ID] column in each row of Table1. However, I don't understand why it works. The documentation says that the filter argument of the calculate function can take a "Table filter" value (I'm guessing this is what's happening here since ID is just a regular column in Table1) but I don't get the explanation in the documentation on what that value needs to be and how to use it to control the formula. If I try to create another calculated column with a different measure (but still using Table1[ID] as the filter condition), I get an error saying that there is a circular reference with my first calculated column. Can someone please explain what's happening here?

[Anonymous · Accepted 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.

[kaisquared90]

That's perfect. Thank you. 

[AntrikshSharma]

@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

[kaisquared90]

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):

CalculatedColumn = 1-CALCULATE(AVERAGE(Table2[Column]), FILTER(Table1, Table1[ID] = EARLIER(Table1[ID])))

Where Table2 is joined to Table1 based in [ID] and AVERAGE(Table2[Column]) is essentially the measure I was trying to calculate before. I think this is how DAX intends users to specify the current row when calculating a measure from another table. In any case, it gives the same values and doesn't give me a circular reference error even if I keep the original calculated column. 

[amitchandak]

@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] )  )

[kaisquared90]

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 🤔