March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I was testing the "Manage aggregations" feature in Power BI Desktop
Tables :
Fact Sale Detail Direct Query => DirectQuery
Fact Sale Agregate Import => Import
Dimension Date => Dual
Dimension Customer => Dual
Dimension City => Dual
The Fact Sale Detail Direct Query and Fact Sale Agregate Import tables have the same database as source, but the Fact Sale Agregate Import is imported, and a group by step agregates it by City Key, Customer Key, Invoice Date Key. That step also sum the Total Excluding Tax and Total Including Tax fields, and creates a CountRows field, with the number of rows agregated.
I clicked on "Manage Agregations" on the Fact Sale Agregate Import table and configure it :
=> Three Group By fields for the three linked dimensions
=> Two Sum fields for the two agregated fields
=> A Count table rows field for the CountRows field in the agregate table
With this configuration i got an error message, please look the attached screenshot
Without the "Count table rows" it worked fine, so the configuration of this field produces the error but i don't know how to fix it.
Please find also the full detail of the error :
Feedback Type:
Frown (Error)
Error Message:
Required property: detailColumnName, was not set in setAggregation.
Stack Trace:
Microsoft.PowerBI.Modeler.ModelerException
at Microsoft.PowerBI.Modeling.Engine.Utilities.SchemaChangePropertyValidator.ValidateRequiredPropertyIsNotNull(PropertyInfo prop, Object parent, String schemaChangeJSName)
at Microsoft.PowerBI.Modeling.Engine.Utilities.SchemaChangePropertyValidator.<>c.<ValidateProperties>b__0_0(ISchemaChange s)
at System.Collections.Generic.List`1.ForEach(Action`1 action)
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 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass66_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__65`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__DisplayClass18_0.<<RouteToLocalModel>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.Modeling.ModelAuthoringRoutingService.<RouteToLocalModel>d__18.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.ModelAuthoringHostService.<ApplyCore>d__8.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.ModelAuthoringHostService.<>c__DisplayClass5_0.<<ModelAuthoringApply>b__2>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.ModelAuthoringHostService.<ExecuteAndShowFrownOnException>d__16`1.MoveNext()
Stack Trace Message:
Required property: detailColumnName, was not set in setAggregation.
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.Windows.Utilities.PowerBIFormUnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.PowerBI.Client.Windows.ModelAuthoringHostService.<ExecuteAndShowFrownOnException>d__16`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.ModelAuthoringHostService.ExecuteAndShowFrownOnException[T](Func`1 func)
at Microsoft.PowerBI.Client.Windows.ModelAuthoringHostService.<>c__DisplayClass5_0.<ModelAuthoringApply>b__1(CancellationToken cancelToken)
at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass9_0`1.<<ExecuteAndHandlePromise>b__0>d.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass9_0`1.<ExecuteAndHandlePromise>b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext()
at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func`1 asyncFunc)
at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass7_0.<ExecuteOnUIThreadAsync>b__0(Object t)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.MinervaDialog.Show(IWindowHandle owner, String dialogDefinitionJson, String urlAllowedForFrame, String urlAllowedForPopup)
at Microsoft.PowerBI.Client.Windows.Services.MinervaModalDialogManager.<>c__DisplayClass9_1.<ShowMinervaModalDialog>b__0(IWindowHandle ownerWindow)
at Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowServiceExtensions.<>c__DisplayClass0_0.<ExecuteWithLegacyOwnerWindow>b__0(IWindowHandle ownerWindow)
at Microsoft.PowerBI.Client.Windows.Services.MinervaModalDialogManager.ShowMinervaModalDialog(String dialogDefinitionJson, ILegacyUIHost uiHost, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, String urlAllowedForFrame, String urlAllowedForPopup, Action onCloseAction)
at Microsoft.PowerBI.Client.Windows.ModalDialogHostService.<>c__DisplayClass10_0.<ShowHostModalDialogCore>b__1()
at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass6_0`1.<ExecuteOnUIThread>b__0()
at Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass5_0.<ExecuteOnUIThread>b__0()
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_1.<Main>b__2()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)
PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.108.997.0","ModuleName":"Microsoft.PowerBI.Modeling.Engine.dll","Component":"Microsoft.PowerBI.Modeling.Engine.Utilities.SchemaChangePropertyValidator","Error":"Microsoft.PowerBI.Modeler.ModelerException - RequiredPropertyNotSet","MethodDef":"ValidateRequiredPropertyIsNotNull","ErrorOffset":"40"}
Snapshot Trace Logs:
C:\Users\XXXXXXXXXXXXXX\Microsoft\Power BI Desktop Store App\FrownSnapShot67a88830-92a6-406a-8315-639445b50b28.zip
Model Default Mode:
Composite
Model Version:
PowerBI_V3
Performance Trace Logs:
C:\Users\XXXXXXXXXXXXXX\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip
Enabled Preview Features:
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_sparklines
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
PowerBINonFatalError_ErrorDescription:
RequiredPropertyNotSet
PowerBIUserFeedbackServices_IsReported:
True
Formulas:
section Section1;
shared #"Fact Sale Detail Direct Query" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
Fact_Sale = #"WideWorldImportersDW-Standard"{[Schema="Fact",Item="Sale"]}[Data]
in
Fact_Sale;
shared #"Dimension City" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
Dimension_City = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="City"]}[Data]
in
Dimension_City;
shared #"Dimension Customer" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
Dimension_Customer = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Customer"]}[Data]
in
Dimension_Customer;
shared #"Dimension Date" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
Dimension_Date = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Date"]}[Data]
in
Dimension_Date;
shared #"Dimension Employee" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
Dimension_Employee = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Employee"]}[Data]
in
Dimension_Employee;
shared #"Dimension Stock Item" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
#"Dimension_Stock Item" = #"WideWorldImportersDW-Standard"{[Schema="Dimension",Item="Stock Item"]}[Data]
in
#"Dimension_Stock Item";
shared #"Fact Sale Agregate Import" = let
Source = Sql.Databases("XXXXXXXXXXXXXX.database.windows.net"),
#"WideWorldImportersDW-Standard" = Source{[Name="WideWorldImportersDW-Standard"]}[Data],
Fact_Sale = #"WideWorldImportersDW-Standard"{[Schema="Fact",Item="Sale"]}[Data],
#"Reordered Columns" = Table.ReorderColumns(Fact_Sale,{"City Key", "Customer Key", "Invoice Date Key", "Sale Key", "Description", "Bill To Customer Key", "Stock Item Key", "Salesperson Key", "WWI Invoice ID", "Delivery Date Key", "Package", "Quantity", "Unit Price", "Tax Rate", "Total Excluding Tax", "Tax Amount", "Profit", "Total Including Tax", "Total Dry Items", "Total Chiller Items", "Lineage Key", "Dimension.City", "Dimension.Customer(Bill To Customer Key)", "Dimension.Customer(Customer Key)", "Dimension.Date(Delivery Date Key)", "Dimension.Date(Invoice Date Key)", "Dimension.Employee", "Dimension.Stock Item"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Sale Key", "Description", "Bill To Customer Key", "Stock Item Key", "Salesperson Key", "WWI Invoice ID", "Delivery Date Key", "Package", "Unit Price", "Tax Rate", "Tax Amount", "Profit", "Total Dry Items", "Total Chiller Items", "Lineage Key", "Quantity"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"City Key", "Customer Key", "Invoice Date Key"}, {{"TotalExcludingTax", each List.Sum([Total Excluding Tax]), type number}, {"TotalIncludingTax", each List.Sum([Total Including Tax]), type number}, {"CountRows", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows";
Do you have an idea ?
Thanks,
Regards,
Romain
Not sure when it happened but sometime after the August 2022 version and the December 2022 version but this now works again as it should.
Same error here. Any solution?
I had the same issue and downgraded my PowerBI Destkop version to May 2022 and it works now.
Good luck.
Thanks at @srkstl . After much effort the only fix for my was a downgrade version. I went to July 2022 and this worked.
I have this exact same issue and nothing can get round it.
I dont think [count table rows] is working whatsoever
@ByTheWay1534 ,Scroll down in that popup and check anything left out. Also, delete the second one and try
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
167 | |
149 | |
99 | |
73 | |
57 |