DAX-Power BI: How to avoid circular dependency in calculated column

Question

I'm working in a report in PowerBi Desktop and I have multiple tables, some have a Period Column with the value but a pair of them don't have it. I can calculate the Period based in the record dates and another table that has the period name and the data ranges. The problem comes when I'm trying to establish the relation between the tables based on the periods due to the circular dependency between the calculated periods and the main table for the relationships. The idea of the relations is to be able to filter the report according to the periods.

To simplify the data, this is the problem: I have an issue (circular dependency) trying to add a relationship between table Periods[PeriodName] and Income[CalculatedPeriod]

I need to be able to use a single filter for both Income and Outcome in the report view. Usually, this is done using the key that relates all the tables. I have no problem doing the relationships between Periods and Outcome (1 : *) because in table outcome, PeriodName is not a calculated column. But, when I try to add the relationship with the Income column (1 : *) a circular dependency is detected. I know is because the [CalculatedPeriod] is calculated using the Periods table but I don't know how to solve this

Periods

PeriodName  StartDate    FinishDate
Period1     2020-Jan-01  2020-Jan-15
Period2     2020-Jan-15  2020-Jan-31
Period3     2020-Feb-01  2020-Feb-28
Period4     2020-Mar-01  2020-Mar-15
Period5     2020-Mar-15  2020-Abr-2

Income

Date        Value [CalculatedPeriod]
2020-Jan-09 25000 Period1
2020-Jan-11  5000 Period1
2020-Jan-28  3000 Period2
2020-Feb-14 18000 Period3
2020-Mar-14 12000 Period4

CalculatedPeriod= CALCULATE(VALUES(Periods[PeriodName]),FILTER(Periods,[Date]>=Periods[StartDate] && [Date] <= Periods[FinishDate]))

Outcome

Date        Value  Period
2020-Jan-07 16000  Period1
2020-Jan-17 11000  Period2
2020-Jan-31  5000  Period3
2020-Feb-14 13000  Period3
2020-Mar-14  9000  Period4

The data has a lot of other columns and the period column is supposed to work as a filter, but if I can't create the relationship with all the tables, the filter will only work with the tables related and will show always the total from the unrelated table.

(Filter: No filters)
Income     Outcome
63000      54000

(Filter: Periods[PeriodName:"Period3"])
Income     Outcome
63000      18000

(Filter: Periods[PeriodName:"Period5"])
Income     Outcome
63000

How can I solve this problem?

So far, the only workaround that I might think is to duplicate the periods table.

Thank you

You need to have defined calendar table, that stores this sort of date and period, then link it to both tables. — Jon

AntrikshSharma AntrikshSharma · Accepted Answer · 2021-04-17T12:58:06

You can use DISTINCT, taking an example for a very simple model if there are 2 tables A and B. And let's say B is created using VALUES ( A[Column] ) and then you build a relationship between A and B then that's not allowed because VALUES depends on the automatic blank row that is added to the one side of the relationship due to invalid realtionship ( caused because of missing values ) meaning there is a possibility that A might contain blank row added because of invalid relationship so if a relationship was possible using VALUES then B would have depended on A and A would depend on B.

CalculatedPeriod =
CALCULATE (
    DISTINCT ( Periods[PeriodName] ),
    FILTER (
        Periods,
        [Date] >= Periods[StartDate]
            && [Date] <= Periods[FinishDate]
    )
)

DAX-Power BI: How to avoid circular dependency in calculated column

1 Answers