topic a circular dependency was detected measure in DAX Commands and Tips

a circular dependency was detected measure

Max_WH — Fri, 22 Dec 2023 09:50:30 GMT

Feedback Type:
Frown (Error)

Timestamp:
2023-12-22T09:33:08.3218659Z

Local Time:
2023-12-22T17:33:08.3218659+08:00

Session ID:
8bf082bf-c1ef-4518-854f-5f200520785b

Release:
November 2023

Product Version:
2.123.742.0 (23.11) (x64)

Stack Trace:
Microsoft.PowerBI.Modeler.ModelingASOperationException
at Microsoft.PowerBI.Modeler.ModelManager.<>c__DisplayClass215_0.<SaveChanges>b__0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.<>c__DisplayClass6_0`1.<RunInActivity>b__0()
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity(ModelingActivityKind activity, Action action)
at Microsoft.PowerBI.Client.Windows.Modeling.Hosting.ModelingTelemetryService.RunInActivity[T](ModelingActivityKind activity, Func`1 action)
at Microsoft.PowerBI.Modeler.ModelEditor.EditMeasureExpression(Measure measure, String expression)
at Microsoft.PowerBI.Modeling.Engine.Authoring.SchemaChangeApplier.Visit(UpdateExpressionSchemaChange schemaChange)
at Microsoft.PowerBI.Modeling.Engine.Authoring.SchemaChangeApplier.Apply(IModelManager modelManager, IEnumerable`1 changes, IRefreshPolicyUpdater refreshPolicyUpdater, IPrivateInformationService privateInformationService)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.<>c__DisplayClass27_0.<ExecuteChange>b__0(IEnumerable`1 changes)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ModelAuthoringTransaction.ExecuteChange(ModelChange modelChange, Boolean isTrustedSource, Boolean saveChange, ISchemaChangeApplierFactory schemaChangeApplierFactory, IRefreshPolicyUpdater refreshPolicyUpdater, IPrivateInformationService piiService, String descriptionToTrace)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.ExtendedModelChangeScope.ExecuteChange(ModelChange modelChange, Boolean isTrustedSource, Boolean saveChange, String descriptionToTrace)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<>c__DisplayClass16_1.<RouteModelChangeAsync>b__3(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<>c__DisplayClass16_1.<RouteModelChangeAsync>b__4(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<>c__DisplayClass11_0.<RunExtendedModelChangeAsync>b__0(IExtendedModelChangeScope scope)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<>c__DisplayClass12_0.<<RunExtendedModelChangeAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Modeling.Engine.Authoring.AsyncModelAuthoringService.<ContinueWithAsyncOperation>d__15.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Modeling.Engine.ModelingEngine.<RouteModelChangeAsync>d__16.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.<>c.<WaitOnUIThreadForTaskCompletion>b__15_0(Task t)
at System.Threading.Tasks.ContinuationResultTaskFromTask`1.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass74_0.<<SynchronizeLooselyWithQueriesAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__73`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringRoutingService.<>c__DisplayClass19_0.<<RouteToLocalModel>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.WaitOnUIThreadForTaskCompletion[T](Task`1 task)
at Microsoft.PowerBI.Client.Windows.CommitHandler.TryCommitPendingUIEdits()
at Microsoft.PowerBI.Client.Windows.MainWindow.OnFileSaving(FileSavingEventArgs eventArgs)
at Microsoft.Practices.Prism.PubSubEvents.EventSubscription`1.<>c__DisplayClass2.<GetExecutionStrategy>b__0(Object[] arguments)
at Microsoft.Practices.Prism.PubSubEvents.EventBase.InternalPublish(Object[] arguments)
at Microsoft.PowerBI.Client.Windows.Services.EventAggregationService.<>c__DisplayClass22_0`2.<PublishAndMarshalExceptions>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.123.742.0","ModuleName":"Microsoft.PowerBI.Modeler.dll","Component":"Microsoft.PowerBI.Modeler.ModelManager+<>c__DisplayClass215_0","Error":"Microsoft.PowerBI.Modeler.ModelingASOperationException","MethodDef":"<SaveChanges>b__0 - PFE_XL_CALCCOLUMN_CIRCULAR_DEPENDENCIES","ErrorOffset":"97","ErrorCode":""}

InnerException0.Stack Trace:
at Microsoft.AnalysisServices.Tabular.Model.SaveChangesImpl(SaveFlags flags, Int32 maxParallelism)
at Microsoft.PowerBI.Modeler.ModelManager.<>c__DisplayClass215_0.<SaveChanges>b__0()

OS Version:
Microsoft Windows NT 10.0.19044.0 (x64 en-US)

CLR Version:
4.8 or later [Release Number = 528372]

Peak Virtual Memory:
72.6 GB

Private Memory:
1.91 GB

Peak Working Set:
1.77 GB

IE Version:
11.3636.19041.0

User ID:
59ab7ff0-29a3-4721-be97-cfff53061e0a

Workbook Package Info:
1* - en-US, Query Groups: 4, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_angularRls
PBI_onObject
PBI_backstageUI
PBI_setLabelOnExportPdf
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
PBI_gitIntegration
PBI_newCard
PBI_buttonSlicerAuthoring
PBI_modelExplorer
PBI_daxQueryView
PBI_aiNarrativesVisual
PBI_qnaImproveLsdlCopilot

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
150%

Supported Services:
Power BI

Re: a circular dependency was detected measure

lbendlin — Mon, 25 Dec 2023 00:58:40 GMT

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

Re: a circular dependency was detected measure

v-zhangti — Tue, 26 Dec 2023 01:51:16 GMT

Hi, Max_WH

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: a circular dependency was detected measure

Max_WH — Tue, 26 Dec 2023 02:28:36 GMT

Iterative Calculation

Hi, Please find the link to the excel sheet above.

Basically, it is the iterative calculation for the 2 red-highlighted, which depend on each other starting from 2024Q1.

The DAX below causing the Circular dependency error.

OP Demand = if (max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])=0,if ([Ref_vs_VCDCNet]>0,[Reference Forecast **bleep** Qty],[Delfor CDFRLO **bleep** Qty]),if ([Ref_vs_VCDCNet]>0,[Reference Forecast **bleep** Qty]-calculate([OP Demand **bleep**],filter(ALLSELECTED('Forecast Time IBPDP'),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=(max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])-1) )),[Delfor CDFRLO **bleep** Qty]-calculate([OP Demand **bleep**],filter(ALLSELECTED('Forecast Time IBPDP'),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=(max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])-1) )))
)

OP Demand **bleep** = calculate(if (max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num])=0,max(IBPDP[BIFO Operational Reference Forecast Qty]),calculate(sum(IBPDP[BIFO Operational Reference Forecast Qty]),ALLEXCEPT('Forecast Time IBPDP','Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]=0)+calculate(max([OP Demand]),ALLEXCEPT('Forecast Time IBPDP','Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]),'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]>0 && 'Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]<=max('Forecast Time IBPDP'[Forecast Time.Forecast Quarter Indicator Num]))))

Re: a circular dependency was detected measure

Max_WH — Tue, 26 Dec 2023 14:34:43 GMT

Iterative calculation in Excel

Hi, Please find the link to the excel sheet above.

Basically, it is the iterative calculation for the 2 red-highlighted, which depend on each other starting from 2024Q1.

The DAX below causing the Circular dependency error.

Re: a circular dependency was detected measure

lbendlin — Tue, 26 Dec 2023 19:17:18 GMT

Let's bring your data into usable format

Then we can add the easy cumulatives

Then for the tricky part we are turning the tables and calculate the cumulative part first.

= Table.AddColumn(#"Added Custom2", "**bleep** OP Demand Should-Be", each if [#"Ref vs VCD-C net"]>0 then [**bleep** Reference Forecast Qty] else [**bleep** Delfor CDFRLO Qty],Currency.Type)

Then from that we get the quarterly demand and the gap.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc6xDcUwCATQXaivODDYZozUUfZf45tIX7aUwsUdepj7FqO1ywWSE85Ej28gggRJefAKv3T1Pap7e0goMgbSrSaOzKy3RfU+TmHQUrPXpH9FW33jIVawXMIq2P+PuUVdq3oIJUY0JGtXbvD8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, #"BIFO Operational Reference Forecast Qty" = _t, #"Delfor CDFRLO Qty" = _t, #"Reference Forecast Qty" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"BIFO Operational Reference Forecast Qty", Currency.Type}, {"Delfor CDFRLO Qty", Currency.Type}, {"Reference Forecast Qty", Currency.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "**bleep** Delfor CDFRLO Qty", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Quarter]<=k[Quarter])[Delfor CDFRLO Qty]),Currency.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "**bleep** Reference Forecast Qty", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Quarter]<=k[Quarter])[Reference Forecast Qty]),Currency.Type), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Ref vs VCD-C net", each [**bleep** Reference Forecast Qty]-[**bleep** Delfor CDFRLO Qty],Currency.Type), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "**bleep** OP Demand Should-Be", each if [#"Ref vs VCD-C net"]>0 then [**bleep** Reference Forecast Qty] else [**bleep** Delfor CDFRLO Qty],Currency.Type), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "OP Demand Should-Be", (k)=> try k[#"**bleep** OP Demand Should-Be"]- Table.LastN(Table.SelectRows(#"Added Custom3",each [Quarter]<k[Quarter]),1){0}[#"**bleep** OP Demand Should-Be"] otherwise k[#"**bleep** OP Demand Should-Be"],Currency.Type), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Op Demand Gap", each [BIFO Operational Reference Forecast Qty]-[#"OP Demand Should-Be"],Currency.Type) in #"Added Custom5"

This way you can avoid the circular reference.

I assume you know what

**bleep**

needs to be replaced with.

Re: a circular dependency was detected measure

Max_WH — Wed, 27 Dec 2023 08:44:53 GMT

Hi Ibendlin, the solution is superb! Can you advise the revised M query if there is another column "Program" in the data? i.e. to be group by "Program" like A, B, C etc.?

Thank you.

Re: a circular dependency was detected measure

lbendlin — Wed, 27 Dec 2023 13:32:39 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: a circular dependency was detected measure

Max_WH — Thu, 28 Dec 2023 04:52:52 GMT

A Matrix with an additional column Program (Group).

Thank you for your advice.

Re: a circular dependency was detected measure

lbendlin — Wed, 27 Dec 2023 15:31:25 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: a circular dependency was detected measure

Max_WH — Wed, 27 Dec 2023 16:02:41 GMT

Hi Sir,

Please find the updated sample data Iterative Circular Calculation with column "Group". Thank you for helping.

Re: a circular dependency was detected measure

lbendlin — Wed, 27 Dec 2023 16:13:07 GMT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBBEsMgCEXv4poFIKgskxtkncn9rxFJW4tps3AG5s9T/9v3tCxLgsTIeZM+WANBg6K/C4IiAiKmAwInG/WhqCdXCkkJTCuYsCcCZubnznkqNXIM5GwrnpQnLvchY+D6wtY59oU/77U75y2IAkcIVTMY+o02Yeu6Ri01mpiXWcvgXlpU53pfLerfbKNe4C5pj1rKrCVwXqJFLS1qqfK/31sLRy0UtHRHgztO", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Quarter = _t, #"BIFO Operational Reference Forecast Qty" = _t, #"Delfor CDFRLO Qty" = _t, #"Reference Forecast Qty" = _t]), #"Changed Type" = Table.TransformColumnTypes( Source, { {"Quarter", type text}, {"BIFO Operational Reference Forecast Qty", Currency.Type}, {"Delfor CDFRLO Qty", Currency.Type}, {"Reference Forecast Qty", Currency.Type} } ), #"Added Custom" = Table.AddColumn( #"Changed Type", "Cumul Delfor CDFRLO Qty", (k) => List.Sum(Table.SelectRows(#"Changed Type", each [Quarter] <= k[Quarter] and [Group]=k[Group])[Delfor CDFRLO Qty] ), Currency.Type ), #"Added Custom1" = Table.AddColumn( #"Added Custom", "Cumul Reference Forecast Qty", (k) => List.Sum( Table.SelectRows(#"Changed Type", each [Quarter] <= k[Quarter] and [Group]=k[Group])[Reference Forecast Qty] ), Currency.Type ), #"Added Custom2" = Table.AddColumn( #"Added Custom1", "Ref vs VCD-C net", each [Cumul Reference Forecast Qty] - [Cumul Delfor CDFRLO Qty], Currency.Type ), #"Added Custom3" = Table.AddColumn( #"Added Custom2", "Cumul OP Demand Should-Be", each if [#"Ref vs VCD-C net"] > 0 then [Cumul Reference Forecast Qty] else [Cumul Delfor CDFRLO Qty], Currency.Type ), #"Added Custom4" = Table.AddColumn( #"Added Custom3", "OP Demand Should-Be", (k) => try k[#"Cumul OP Demand Should-Be"] - Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Group]=k[Group]), 1){0}[ #"Cumul OP Demand Should-Be" ] otherwise k[#"Cumul OP Demand Should-Be"], Currency.Type ), #"Added Custom5" = Table.AddColumn( #"Added Custom4", "Op Demand Gap", each [BIFO Operational Reference Forecast Qty] - [#"OP Demand Should-Be"], Currency.Type ) in #"Added Custom5"

Re: a circular dependency was detected measure

Max_WH — Sat, 30 Dec 2023 00:30:36 GMT

Hi Ibendlin, Thank you once again! But the "**bleep** OP demand should-be" of the very first quarter is equal to "BIFO Operational Reference Forecast Qty". Can you help to enlighten?

Re: a circular dependency was detected measure

lbendlin — Thu, 28 Dec 2023 14:18:16 GMT

This is taken care of here

#"Added Custom4" = Table.AddColumn(
    #"Added Custom3", 
    "OP Demand Should-Be", 
    (k) =>
      try
        k[#"Cumul OP Demand Should-Be"]
          - Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Group]=k[Group]), 1){0}[
            #"Cumul OP Demand Should-Be"
          ]
      otherwise
        k[#"Cumul OP Demand Should-Be"], 
    Currency.Type
  ),

via the try ... otherwise ...

Re: a circular dependency was detected measure

Max_WH — Fri, 29 Dec 2023 17:10:09 GMT

Hi Ibendlin,

Thank you very much for your help! Appreciated much!

I have changed slightly the below to accomodate the "Cumul OP demand should-be" of the very first quarter equal to "BIFO Operational Reference Forecast Qty".

Happy new year and best wishes!

#"Added Custom3" = Table.AddColumn(
#"Added Custom2",
"Cumul OP Demand Should-Be",
each
if [#"Forecast Time.Forecast Quarter Indicator Num"]<=0 then
[BIFO Operational Reference Forecast Qty]
else
if [#"Ref vs VCD-C net"] > 0 then
[Cumul Reference Forecast Qty]
else
[Cumul Delfor CDFRLO Qty],
Int64.Type
),
#"Added Custom4" = Table.AddColumn(
#"Added Custom3",
"OP Demand Should-Be",
(k) =>
try
k[#"Cumul OP Demand Should-Be"]
- Table.LastN(Table.SelectRows(#"Added Custom3", each [Quarter] < k[Quarter] and [Product]=k[Product]), 1){0}[
#"Cumul OP Demand Should-Be"
]
otherwise
if k[#"Ref vs VCD-C net"] > 0 then
k[#"Cumul Reference Forecast Qty"]
else
k[#"Cumul Delfor CDFRLO Qty"],
Int64.Type
),