Circular Dependency Calculated Column Power BI Desktop

Question

In my Calculated Table, variable AsOfDate should be reflected by slicer dim_Date which is also calculated table.

Calculated table CrossTableEffectiveDate derived from table fact_Premium that have Date relationship to dim_Date.

I got error: Circular Dependency Calculated Column

CrossTableEffectiveDate = 
    VAR AsOfDate = VALUE("2019-01-31") //This value should be based on date value in a slicer
    VAR CrossTables =
        CROSSJOIN(

            SUMMARIZE(fact_Premium, 
                fact_Premium[PolicyNumber],
                fact_Premium[CompanyLocationGuid],
                fact_Premium[Coverage],
                fact_Premium[State],
                fact_Premium[SICCode],
                fact_Premium[ASLOB],
                fact_Premium[ProducerGUID],
                "Start", MIN(fact_Premium[EffectiveDate]),
                "End", MAX(fact_Premium[ExpirationDate]),
                "Premium", SUM(fact_Premium[Premium])
                    ),
            'Calendar')
VAR RiskPeriods =
        ADDCOLUMNS(
            FILTER(CrossTables,
                'Calendar'[EoMonth] >= [Start] && 'Calendar'[Month] <= [End] && 'Calendar'[Month] <= AsOfDate),
            "StartRiskMonth", IF([Start] > 'Calendar'[Month], [Start], 'Calendar'[Month]),
            "EndRiskMonth", IF([End] < 'Calendar'[EoMonth], [End], 'Calendar'[EoMonth])
                 )
    RETURN SELECTCOLUMNS(RiskPeriods,
                "PolicyNumber", fact_Premium[PolicyNumber],
                "CompanyLocationGUID", fact_Premium[CompanyLocationGuid],
                "Coverage",fact_Premium[Coverage],
                "State", fact_Premium[State],
                "SICCode",fact_Premium[SICCode],
                "ASLOB", fact_Premium[ASLOB],
                "ProducerGUID",fact_Premium[ProducerGUID],
                "StartRiskMonth", [StartRiskMonth],
                "EndRiskMonth", [EndRiskMonth],
                "YearNum", YEAR('Calendar'[Month]),
                "Qtr", ROUNDUP(MONTH('Calendar'[Month])/3, 0),
                "MonthNum", MONTH('Calendar'[Month]),
                "WrittenPremium", [Premium],
                "DaysInMonth", [EndRiskMonth] - [StartRiskMonth] + 1,
                //"EndRiskMonth-Start", [EndRiskMonth] - [StartRiskMonth] + 1,
                //"End-Start",[End] - [Start] + 1,
                "EarnedPremium", [Premium] *
                    DIVIDE([EndRiskMonth] - [StartRiskMonth] + 1, [End] - [Start] + 1))

How can I make AsOfDate variable be referenced by Date slicer from dim_Date?

What dependencies does `dim_Date` have? – Alexis Olson Jan 23 '19 at 21:58 — Alexis Olson, Jan 23 '19 at 21:58

score 2 · Accepted Answer · answered Jan 23 '19 at 22:04

2

You cannot have a calculated table or calculated column be dependent on a slicer.

These are only calculated once per data load and are not responsive to any filtering you have in your report.

You can add your support to this idea to improve the chances of Microsoft implementing it.

Dynamic calculated column

answered Jan 23 '19 at 22:04

Alexis Olson

38,724
7
42
64

Thank you . Hopefully Microsoft will implement it soon. – Serdia Jan 24 '19 at 00:19
I do not think it is still valid what you wrote here. I often create calculated tables like these here https://stackoverflow.com/a/57694103/1903793 I use slicers on them. However I ran into problem of circular dependency here https://stackoverflow.com/q/58650531/1903793 I do not know if it is DAX or something else. – Przemyslaw Remin Oct 31 '19 at 20:18
You can certainly use slicers to filter existing calculated tables but you cannot use slicers as part of a calculated table's *definition*. – Alexis Olson Oct 31 '19 at 20:30

Circular Dependency Calculated Column Power BI Desktop

1 Answers1