A circular dependency has been detected adding one column to a calendar table

[DBrito79]

Hello!!

I have a large database of animals on a farm. I have the following measure to make the inventory that works for me however it takes a long, long time to finish. I had plans to put this formula in the PBIx calendar table adicinal column and thus have the values already calculated in a table in the hope of reducing time in inventory calculations and subsequent formulas with this data. I don't know if I'll succeed.
However, when I place a column with the formula below in the calendar table with this measure, I get the error :

A circular dependency has been detected: Calendar[Column].

Do you think it's possible to transform this DAX formula so that it doesn't have the circular dependency? I'd like to avoid making another inventory table, that works, although when related to the calendar table (1-1) it gives the same circular dependency error. And I´ll get a lot of difficulties in fixing all relationships.. I'm really at a blind spot with this step.

Thank you
Best regards 

INVENTÁRIO PORCAS =

VAR MatrizesEntradas =

    CALCULATE(

        CALCULATE(

            COUNT(SOWS[Mating]),

            USERELATIONSHIP('Calendar'[Date], SOWS[Mating]),

            USERELATIONSHIP('FARM SETTINGS'[SiteId], SOWS[SiteId])

        ),

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] <= MAX('Calendar'[Date])

        ),

        USERELATIONSHIP('Calendar'[Date], SOWS[Mating])

    )

VAR MatrizesSaidas =

    CALCULATE(

        CALCULATE(

            DISTINCTCOUNTNOBLANK(CYCLES[ID&FARM]),

            NOT(ISBLANK(CYCLES[Dia Saida])),

            USERELATIONSHIP('Calendar'[Date], CYCLES[Dia Saida])

        ),

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] <= MAX('Calendar'[Date])

        )

    )

RETURN

    CALCULATE(

        MatrizesEntradas - MatrizesSaidas,

        FILTER(

            ALL('Calendar'),

            'Calendar'[Date] <= MAX('Calendar'[Date])

        )

    )

[DBrito79]

Hello Lbendin!
First of all thanks for the quick reply. Yes, I already have this done in the Power Query Editor and it solves the problem of speed without a doubt. However, and correct me if I'm wrong, with the inventory table in Power Query, I no longer have the freedom to use filters in the dashboards, do I? The Sows table and the Cycles table, (one is the history of entries, exits, deaths, causes... of each animal, the other is, line by line, the cycle -> more or less the result of its production for each age group of an animal) contain variables such as different genetics, types of animals, age groups, barns, farms, among others that by filtering these variables, I can create recalculated inventories with those DAX measures. My aim in making a DAX table (something with data already calculated as a temporary table) within Power BI is to have an intermediate level of speed between the DAX measures that I have and the "static" table without the possibility of filter analysis in the Power Query Editor. I don't know if I've made myself clear, I don't know if this is the way to make a temporary table, I don't even know if it's possible to do this to maximize speed and have access to filters at the same time... My level of knowledge of Power BI has been very autodidact.

It's just that with inventory KPIs with DAX measures, the filters work well but the dashboards sometimes take minutes to open, and if I put in periods of years (there are 20-year history in the database) the system says it can't run due to lack of resources...
Can you tell me if you have any brilliant ideas for this or if it's a topic to give up and end the idea of being able to filter inventories by variables? ....
Thank you
Regards

[DBrito79]

Sorry @lbendlin I'm reading your proposal more carefully and I think I've misunderstood...
What you're advising is to put the calendar table in Power Query and then add the inventory columns to this level of DAX (Power BI), do you think this solves the problem of the filters?
I'll give it a try! I didn't see it that way!
Thank you.

[lbendlin]

My advice is to use an external calendar table. An Excel file on a SharePoint, for example.

[DBrito79]

Hello @lbendlin , apologies for the delay. I've integrated the calendar into PQE, and it has improved the PBIX file, but I'm still encountering the same error, "circular dependency has been detected," when I add inventory DAX formulas in columns within that table. These formulas contain accumulation calculations (such as the part highlighted in red) based on the calendar table, which is located in Power Query despite these columns being in Power BI.

FILTER(

            ALL('Calendar'),

            'Calendar'[Date] <= MAX('Calendar'[Date])

I'd like to emphasise once again that what I wanted, although I don't know if it's effective, was to put these inventory values already calculated for each day in a power bi table so that I could use certain filters that a PQE table doesn't allow and at the same time be faster than using the individual measurements that take a long time to appear...

Let me know if you think I'm on the right track to get there. An inventory time table made up of DAX formulas in Power BI that speeds up calculations but can be altered by filters if necessary...
And thanks for your patience

[lbendlin]

You can change your formula to use variables.

var md = max('Calendar'[Date])
return FILTER(ALL('Calendar'[Date]),

            [Date] <= md)

[lbendlin]

Do you think it's possible to transform this DAX formula so that it doesn't have the circular dependency?

Push this upstream to Power Query.  In fact, push this further upstream.  There is no good reason for creating a calendar in either Power BI or Power Query, especially if you have a fiscal calendar.