Circular dependency calculated column

[Anonymous]

I have a measure for which I then have a calculated column that groups rows depending of the value that the meaure returns.

I then have a table that I created in Power BI with the purpose of sorting these groups, so that they come in the correct order when in a visual. 

However, when I'm trying to create a relationship between the fact table (with measure and calc column), I get an error saying "A circular dependency was detected", and then refers to the calculated column twice.

I don't really understand what I'm doing wrong? 

[joaoribeiro]

Hi @Anonymous ,

Could you please share some screenshots or the measures you used to create the calculated column and the table?

In advance, when you have these circular dependency errors, a way to avoid that is to use the whole formula of the calculated column inside the code used to create the column for sorting, instead of reffering to the calculated column.

If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!
Best regards,
Joao Ribeiro

[Anonymous]

@joaoribeiro Thanks,

Sure, here's the measure (rewritten for this)

Measure = 
var case = MIN('FactTable'[ID_col])
var end = CALCULATE(MIN('FactTable'[enddate]), 'Dim_table1'[col1] = "X", FILTER('FactTable','FactTable'[ID_col] = Y))
VAR abc = CALCULATE(MIN('FactTable'[date]), FILTER('FactTable', 'FactTable'[ID_col] = Y))


VAR XYZ =
    IF (
        ISBLANK ( end) ||  ISBLANK ( abc) 
            || end = ( abc - 1 ) || abc = end,
        BLANK(),
        CALCULATE (
            COUNTROWS ( 'Dim Calendar' ),
            DATESBETWEEN (
                'Dim Calendar'[Date],
                abc,
                end
            ),
            'Dim Calendar'[WorkDay] = "Y",
            ALL ( 'FactTable')
        ) 
    )
RETURN
   
    XYZ

Calc col:

Calc Col = 
IF('Fact Table'[Measure] = 0, "0",
IF('Fact Table'[Measure]  >= 1 && 'Fact Table'[Measure]  <= 2, "1-2",
IF('Fact Table'[Measure] >= 3 && 'Fact Table'[Measure]  <= 5, "3-5",
IF('Fact Table'[Measure]  >= 6 && 'Fact Table'[Measure]  <= 10, "6-10",
IF('Fact Table'[Measure]  >= 11 , "11+"
)))))

Created Table:

[joaoribeiro · Accepted solution]

Thank you @Anonymous ,

I suggest you to try creating the sorting column directly from the original table, with the calculation of the grouppering inside it's formula, as shown below. After that, sort the gruppering calculated column by the new sorting column:

Sorting Gruppering = 

SWITCH(
TRUE(),
'Fact Table'[Measure] = 0, 0,
'Fact Table'[Measure]  >= 1 && 'Fact Table'[Measure]  <= 2, 1,
'Fact Table'[Measure] >= 3 && 'Fact Table'[Measure]  <= 5, 2,
'Fact Table'[Measure]  >= 6 && 'Fact Table'[Measure]  <= 10, 3,
'Fact Table'[Measure]  >= 11 , 4
)

With that, you should not have circular refference issues between the columns, as they are not reffering to each other.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!
Best regards,
Joao Ribeiro

[Anonymous]

Making the measure a calculated column instead helped with circular dependency

[joaoribeiro]

Yes, it's better when you have these well defined rules. By creating the measure without mentioning the other column will avoid the circular dependency.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro