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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Unexpected Error. A connection cannot be made. Ensure that the server is running.

I have a data connection with around 10,000 rows and 9 columns. When I update it, I get this error:

 

"Unexpected Error. A connection cannot be made, Ensure that the server is running."

 

The thing is, it's just pulling from a csv on a shared drive. And when I pare the data down a bit to 9,700 rows or so, it updates without a problem (it takes a minute or two, but it updates). 

 

When I copy the error details into a note file, I see in the trace that "No connection could be made because the target machine actively refused it." 

 

Any idea what might be going on here?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Well, I finally "fixed" this issue. The only thing I can figure is that the data processing requirements were somehow too great, and it was throwing errors as a result.

 

What finally fixed the issue was removing some complex calculated columns. I had three of them that were using CALCULATE with ALL and two sets of EARLIER filters each, and once they were removed, the refresh worked just fine.

 

Like I mentioned, the other thing that was fixing the issue was if my 10,000-row source dataset was smaller, like 9,700 rows. Which seemed strange because at 9 columns, that's not huge.

 

But it loaded at the smaller size no matter which 300 rows I removed, so it wasn't a matter of irregular data in those extra rows.

 

What it added up to was that Power BI was choking on the size of the calculations. Those EARLIER filters can't be cheap given the way they have to reference every row in the table for each row that it calculates. So 10,000 rows * 10,000 rows * 3 fields * 2 uses of EARLIER instances in each = 600,000,000 rows to scan every time it loads.

 

But still, it should be built to handle it, and not error out. Especially with misleading errors.

 

Edit:

I found I was able to replace my many CALCULATE + EARLIER columns with a single INDEX column that uses two EARLIER clauses, and for everything else, I'm using LOOKUPVALUE and referencing the index to find what I need. And where my data refreshes before would take multiple minutes or error out, they now update in a flash 🙂

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Well, I finally "fixed" this issue. The only thing I can figure is that the data processing requirements were somehow too great, and it was throwing errors as a result.

 

What finally fixed the issue was removing some complex calculated columns. I had three of them that were using CALCULATE with ALL and two sets of EARLIER filters each, and once they were removed, the refresh worked just fine.

 

Like I mentioned, the other thing that was fixing the issue was if my 10,000-row source dataset was smaller, like 9,700 rows. Which seemed strange because at 9 columns, that's not huge.

 

But it loaded at the smaller size no matter which 300 rows I removed, so it wasn't a matter of irregular data in those extra rows.

 

What it added up to was that Power BI was choking on the size of the calculations. Those EARLIER filters can't be cheap given the way they have to reference every row in the table for each row that it calculates. So 10,000 rows * 10,000 rows * 3 fields * 2 uses of EARLIER instances in each = 600,000,000 rows to scan every time it loads.

 

But still, it should be built to handle it, and not error out. Especially with misleading errors.

 

Edit:

I found I was able to replace my many CALCULATE + EARLIER columns with a single INDEX column that uses two EARLIER clauses, and for everything else, I'm using LOOKUPVALUE and referencing the index to find what I need. And where my data refreshes before would take multiple minutes or error out, they now update in a flash 🙂

jbocachica
Resolver II
Resolver II

Hi, this could be because you are using a network path, for all my customers with this kind of scenarios, I strongly recommend them to use OneDrive for Business or a Sharepoint library, however, if you cannot use those kind of products to store your CSV file, please try assigning a letter to your network path and then changing your Power BI connection to use that path.

 

net use x: \\localhost\c$\Folder\Example

 

In this case, you will use the letter X as a drive.

 

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co

Hi @jbocachica,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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