Surprising No connection to target machine error or cant retrieve data for visual

[masplin]

Hi

Really foxed by this.

I have built a model that is using 3 other power BI datasets as its datasource.  Bit complicated using it as have 3 date tables (Transactions, CRM and Employee System (EP)), 2 client table, 2 employee . Anyway I've got it working, but hit an issue trying to create a report that looks at whether records formt he mployee database have transacted.  it was fine, but made a few changes and constantly getting this error "The following system error occurred: No connection could be made because the target machine actively refused it. "

If i close and reopen its ok until I try to add another measure. Or if I change the date slicer or branch slicer.  Also wird things where simple measures aren't right. 

For example i have this stable, but % PP is just so shoudl be 100% on all rows

% PP = DIVIDE([PP Records],[Ready in Month],BLANK())

Ready in Month is a measure in the EP system,

PP Records is this as there is  unique identifier "Eploy ID" in both the transaction (PP) dataset and EP dataset. I cant use a relationship as have a datetable relationship preventing this. However seems to work fine. 

PP Records = CALCULATE(SUMX(ADDCOLUMNS(SUMMARIZE(Applications,Applications[Eploy ID],Applications[Ready Date]),
                              "In PP Work",CALCULATE(COUNTA('Employees PP'[Start Date]),FILTER('Employees PP',
                                                                                                'Employees PP'[Eploy ID]=Applications[Eploy ID] && 
                                                                                                'Employees PP'[Start Date]>=Applications[Ready Date]-30))
                                ),
                                [In PP Work]
                    ),USERELATIONSHIP(Applications[Ready Date],'DateTable EP'[Date]))

If i change date a few times and branch eventually it errors as follows or the target machine error

Feedback Type:
Frown (Error)

Timestamp:
2023-04-27T16:44:21.2183245Z

Local Time:
2023-04-27T17:44:21.2183245+01:00

Session ID:
7e3dce40-5449-4b9b-b16f-d4f8d9f7e494

Release:
April 2023

Product Version:
2.116.884.0 (23.04) (x64)

Error Message:
Couldn't retrieve the data for this visual. Please try again later.

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

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

Peak Virtual Memory:
70.5 GB

Private Memory:
767 MB

Peak Working Set:
1.03 GB

IE Version:
11.789.19041.0

User ID:
5c8a603f-eac4-49eb-b300-a6bcbc1ee1ad

Workbook Package Info:
1* - en-GB, Query Groups: 0, fastCombine: Enabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\MikeAsplin\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot23808ead-f7a3-46df-8ef1-7e9907f73e1f.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\MikeAsplin\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_enableWebView2
PQ_WebView2Connector
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_horizontalFusion
PBI_optimizeTabRibbon
PBI_relationshipEditPane
PBI_setLabelOnExportPdf

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_angularRls
PBI_onObject
PBI_dynamicFormatString

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared #"DirectQuery to AS - Trinity PowerBI" = let
Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/Trinity%20Reporting", "Trinity PowerBI"),
Cubes = Table.Combine(Source[Data]),
Cube = Cubes{[Id="Model", Kind="Cube"]}[Data]
in
Cube;

shared #"DirectQuery to AS - CRM" = let
Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/Trinity%20Reporting", "CRM"),
Cubes = Table.Combine(Source[Data]),
Cube = Cubes{[Id="Model", Kind="Cube"]}[Data]
in
Cube;

shared #"DirectQuery to AS - Eploy Power BI" = let
Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/Trinity%20Reporting", "Eploy Power BI"),
Cubes = Table.Combine(Source[Data]),
Cube = Cubes{[Id="Model", Kind="Cube"]}[Data]
in
Cube;

Is all this some kind of memory failure as measure s are too complicated /badly written so it melts down? i'm very new to using models joined together and making it up how to created these combined measures. 

afraid can't share the model as full of confidential data and i suspect if i slimmed it down the error would go away!!!  It's linke dot production datasets so not sure if I can switch datsets to some smaller version of production without loosing the features i've added to combined model. 

Any thoughts appreciated as stopped me in my tracks with no way out. 

Thanks

Mike

[lbendlin]

It depends on how you join these data models. The most critical factor is the cardinality of the join columns. You really, really want to keep these below 10000.

[masplin]

Hmmm yup got about 10,000 employees and 12,000 clients all with unique IDs.  so this is essentially a memory failure.  So not a great tool for doining something like merging a CRM with a transactional DB.

Nto sure what i do about that, but thanks anyway

[lbendlin]

It's not a great tool for merging anything that has large cardinality. This is true inside a data model as well. 

Then there is this little nugget:  Manage DirectQuery connections to a published dataset - Power BI | Microsoft Learn