Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Circular dependency calculated column

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? 

Need Help
  • joaoribeiro's avatar
    joaoribeiro
    3 years ago

    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

5 Replies

  • joaoribeiro's avatar
    joaoribeiro
    Impactful Individual
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        joaoribeiro
        Impactful Individual
        3 years ago

        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