Forum Discussion
Circular dependency with calculated tables
Hi,
I am having a hard time to understand/overcome the below situation:
- I have a fact table ('buyers')
- based on which I created a date table with DAX ('date')
date =
CALENDAR (
FIRSTDATE ( 'buyers'[date] ),
DATE ( YEAR ( LASTDATE ( 'buyers'[date] ) ), MONTH ( LASTDATE ( 'buyers'[date] ) ) + 6, DAY ( LASTDATE ( 'buyers'[date] ) ) )
)
with some additional columns:
year = YEAR('date'[Date])
month = MONTH('date'[Date])
yearmonth = 'date'[year]&'date'[month]
- and a 3rd table, using SUMMARIZECOLUMNS on the above tables
sales = SUMMARIZECOLUMNS (
'buyers'[id],
'date'[year],
'date'[month],
"quantity (corrected)",
VAR _id =
DISTINCT ( buyers[id] )
RETURN
SWITCH (
_id,
"*1",
[Sales (original)]
- CALCULATE (
[Sales (original)],
FILTER ( ALLNOBLANKROW ( 'buyers'[id] ), 'buyers'[id] = "#1" )
),
"*2",
[Sales (original)]
- CALCULATE (
[Sales (original)],
FILTER ( ALLNOBLANKROW ( 'buyers'[id] ), 'buyers'[id] = "#2" )
),
"*3",
[Sales (original)]
- CALCULATE (
[Sales (original)],
FILTER ( ALLNOBLANKROW ( 'buyers'[id] ), 'buyers'[id] = "#3" )
),
[Sales (original)]
)
)
Afterwards I added a calculated column on the 'sales' table as:
yearmonth = sales[year]&sales[month]
Here I wanted to have a relationship between the two 'yearmonth' columns (sales and date) but instead I get the below error message:
What I have already done 😅:
- changed VALUES to DISTINCT - as visible above
- changed/expanded CALCULATE with FILTER(ALLNOBLANKROW...) - as visible above
- watched Avoiding circular dependency errors in DAX
- read most of Understanding circular dependencies in DAX and Avoiding circular dependency errors in DAX
- and also a lot of community posts.
Maybe I am blind, but cannot understand why this is not working as I imagine it.
Any suggestion or lesson to learn here?
Thanks,
R.
2 Replies
- samdthompsonMemorable Member
Hello, try making your calendar like this:
date = CALENDARAUTO()
the calendar will return all the continusous dates from the smallest to largest anywhere in the model and wont have the problem with circular dependencies.
- Ashish_MathurSuper User
Hi,
It will be nice if you can share some data, describe the question and show the expected result.