Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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

  • samdthompson's avatar
    samdthompson
    Memorable 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.

     

     

  • Hi,

    It will be nice if you can share some data, describe the question and show the expected result.