Forum Discussion
Circular dependency calculated column
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
Impactful IndividualHi 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
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
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 RibeiroMaking 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