A circular dependency in Power Bi

user_7862953 0
Viewed 362

I calculated a field that brings the total sales for bikes in 2018-all work fine copy the same formula to another column to calculate the same for 2017 brought this reference error -how can I move around it if I want to show a few columns using the same formula with different parameters

Sales_Bike_2018 =
CALCULATE (
    [Total_Sales],
    TrainingSample2[Business Segment] = "Bikes",
    TrainingSample2[Year] = 2018
)

Sales_Bike_2017 =
CALCULATE (
    [Total_Sales],
    TrainingSample2[Business Segment] = "Bikes",
    TrainingSample2[Year] = 2017
)
dax powerbi calculated-columns
1 Answers

The problem is that you are creating 2 columns on a table that doesn't have a Unique Key.

When you create Sales_Bike_2018 everything works fine because it depends on the rest of the columns of your table but when you create the column Sales_Bike_2017, the code of Sales_Bike_2017 depends on rest of the columns as well as on Sales_Bike_2018.

Similary if it was possible to create Sales_Bike_2017 then Sales_Bike_2018 would have depended upon Sales_Bike_2017 and that's not allowed and that's why you get a circular dependency error.

Solution: Remove filters coming from both new columns due to context transition by using REMOVEFILTERS ()

Sales_Bike_2018 =
CALCULATE (
    [Total_Sales],
    TrainingSample2[Business Segment] = "Bikes",
    TrainingSample2[Year] = 2018,
    REMOVEFILTERS ( TrainingSample2[Sales_Bike_2017] )
)

Sales_Bike_2017 =
CALCULATE (
    [Total_Sales],
    TrainingSample2[Business Segment] = "Bikes",
    TrainingSample2[Year] = 2017,
    REMOVEFILTERS ( TrainingSample2[Sales_Bike_2018] )
)
user_149049680
Related