Problems when creating relationships between two tables (3 million records vs 5 million records)

[jninop]

I have a very weird issue. I want a one to many relationship between two tables one with 3,219,057 records and the another with 4.5 million and growing every day. The small table has a unique id for each register, I have already verified counting unique values in Powerbi, R and python. When I try the create the relationship, Powerbi shows me a msg saying 

Feedback Type:
Frown (Error)

Error Message:
Failed to save modifications to the server. Error returned: 'Relationship 'c526d2eb-78f0-4719-9438-655026aceb8f' between columns 'bills-v2[Emisor]' and 'rutfe[Nit]' has the TO end cardinality set to Many. TO end cardinality must always be set to One.
'.

Stack Trace:
   at Microsoft.AnalysisServices.Tabular.Model.SaveChanges(SaveOptions saveOptions)
   at Microsoft.PowerBI.Modeler.Relationships.CreateRelationship(Column fromColumn, Column toColumn, Boolean isActiveSuggestion, Boolean isAutoCrossFiltering, Boolean isAutoDirection, Boolean referentialIntegrity, Nullable`1 fromCardinality, Nullable`1 toCardinality, Nullable`1 crossFilteringBehavior, Nullable`1 securityFilteringBehavior)
   at Microsoft.PowerBI.Modeler.ModelManager.CreateRelationship(Column fromColumn, Column toColumn, Boolean isActiveSuggestion, Boolean isAutoCrossFiltering, Boolean isAutoDirection, Boolean referentialIntegrity, Nullable`1 fromCardinality, Nullable`1 toCardinality, Nullable`1 crossFilteringBehavior, Nullable`1 securityFilteringBehavior)
   at Microsoft.PowerBI.Client.Windows.Modeling.SchemaChangeApplier.Visit(CreateRelationshipSchemaChange schemaChange)
   at Microsoft.PowerBI.Client.Windows.Modeling.SchemaChangeApplier.Apply(IModelManager modelManager, IReportManager reportManager, IEnumerable`1 changes)
   at Microsoft.PowerBI.Client.Windows.Modeling.ModelAuthoringExceptionHandler.AddModelChangeResultToExceptions(Action action, ModelChangeResult modelChangeResult)
   at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ModelAuthoringTransaction.ExecuteChange(ModelChange modelChange)
   at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.ExtendedModelChangeScopeBase.ExecuteChange(ModelChange modelChange)
   at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClassf.<RunExtendedModelChangeAsync>b__e()
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__3.MoveNext()

Stack Trace Message:
Failed to save modifications to the server. Error returned: 'Relationship 'c526d2eb-78f0-4719-9438-655026aceb8f' between columns 'bills-v2[Emisor]' and 'rutfe[Nit]' has the TO end cardinality set to Many. TO end cardinality must always be set to One.
'.

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.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception)
   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__DisplayClass1.<HandleException>b__0()
   at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)
   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.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
   at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
   at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
   at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAwaitableAsyncExceptions>d__3.MoveNext()
   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.Runtime.CompilerServices.AsyncMethodBuilderCore.MoveNextRunner.Run()
   at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
   at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
   at System.Windows.Threading.DispatcherOperation.InvokeImpl()
   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 MS.Internal.CulturePreservingExecutionContext.Run(CulturePreservingExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Windows.Threading.DispatcherOperation.Invoke()
   at System.Windows.Threading.Dispatcher.ProcessQueue()
   at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
   at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
   at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
   at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
   at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
   at MS.Win32.HwndSubclass.SubclassWndProc(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.WebDialogs.WebDialog.<>n__FabricatedMethod8(IWindowHandle )
   at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass7.<HandleExceptionsWithNestedTasks>b__6()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)

[v-qiuyu-msft]

Hi @jninop,

Please go to Query Editor, select the column which you treat as One side in the relationship, then click Remove Duplicates, save and apply the change to data model, then create the relationship again.

Best Regards,
Qiuyun Yu