Forum Discussion

Max_WH

Helper I

2 years ago

Solved

a circular dependency was detected measure

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

lbendlin

2 years ago

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"

14 Replies

lbendlin
Super User
2 years ago
Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?
- Max_WH
  Helper I
  2 years ago
  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.
  
  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]))))

v-zhangti

Community Support

2 years ago

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.

Max_WH

Helper I

2 years ago

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]))))

lbendlin

Super User

2 years ago

Let's bring your data into usable format

Then we can add the easy cumulatives

 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) in #"Added Custom2"

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.

Forum Discussion

a circular dependency was detected measure

14 Replies

Related content

A Circular Dependency was detected

Circular dependency

A circular dependency was detected

Error: A circular dependency was detected

A circular dependency was detected