Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
kaisquared90
Regular Visitor

CALCULATE Filter argument is just a column

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?
1 ACCEPTED SOLUTION
Anonymous
Not applicable

// 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.

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

// 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. 

AntrikshSharma
Super User
Super User

@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

1.PNG2.PNG3.PNG5.PNG6.PNG

 

 

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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 🤔

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.