CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Serdia
Serdia

Reputation: 4428

Circular Dependency Calculated Column Power BI Desktop

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

enter image description here

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

Upvotes: 1

Views: 3337

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40264

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

Upvotes: 2

Related Questions