Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ByTheWay1534
Frequent Visitor

Manage aggregations Error Count Table Rows : detailColumnName, was not set in setAggregation

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.

 

2022-09-07 16_57_29-DirectQueryManageAgregations - Power BI Desktop.png

 

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

6 REPLIES 6
fraserward
Frequent Visitor

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. 

gtacchini
Advocate I
Advocate I

Same error here. Any solution?

srkstl
New Member

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.

fraserward
Frequent Visitor

I have this exact same issue and nothing can get round it. 

 

I dont think [count table rows] is working whatsoever

amitchandak
Super User
Super User

@ByTheWay1534 ,Scroll down in that popup and check anything left out. Also, delete the second one and try

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.