Couldn't load the data for this visual Error

[Anonymous]

I am linking 2 DBs via the Relationship function. When trying to add fields from 1 of the DBs to a report, the report errors out & returns a 'Cant display the visual' message. There are no calculated columns in either datasets that would cause a major spike in memory usage when modeling. I have 8 GB of RAM - I'm unsure how to move forward, anyone have any suggestions?

[Anonymous · Accepted solution]

I ended up creating a measure in the Bank DB for the mortgage loan total amount. That seemed to solution my problem of erroring out due to lack of memory. Adding fields from either table is snappier.

[KHorseman]

When you hit the details button, is it specifically telling you this is a memory problem? It may simply be that you're putting columns together in a way that your model can't aggregate because of the relationships you have or haven't created. What is the exact error message it's giving you?

[Anonymous]

'Couldn't load the data for this visual

There's not enough memory to comltete this operation. Pleas try again later when there may be more memory available.'

I am then prompted to Close ot Send a frown.

Below is the details of the error.

Feedback Type:
Frown (Error)

Timestamp:
2017-09-19T14:25:09.8962730Z

Local Time:
2017-09-19T10:25:09.8962730-04:00

Product Version:
2.50.4859.502 (PBIDesktop) (x64)

Release:
September 2017

IE Version:
11.1715.14393.0

OS Version:
Microsoft Windows NT 10.0.14393.0 (x64 en-US)

CLR Version:
4.6.2. or later [Release Number = 394802]

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Peak Working Set:
669 MB

Private Memory:
482 MB

Peak Virtual Memory:
34 GB

Error Message:
There's not enough memory to complete this operation. Please try again later when there may be more memory available.

User ID:
7b58882d-be19-408a-81f5-de695339f5ff

Session ID:
1c9acc77-9c64-43ef-a463-d4c4756464b6

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\actasson\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1374041188.zip

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

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

Activity ID:
null

Time:
Tue Sep 19 2017 10:23:37 GMT-0400 (Eastern Daylight Time)

Error Code:
QuerySystemError

OData Error Message:
Failed to execute the DAX query.

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared Encompass = let
    Source = Excel.Workbook(File.Contents("C:\Users\actasson\Desktop\Encompass.xlsx"), null, true),
    Encompass_Sheet = Source{[Item="Encompass",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Encompass_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Borrower Last Name", type text}, {"Co-Borrower Last Name", type text}, {"Borr SSN", type text}, {"Co-Borr SSN", type text}, {"Loan Number", Int64.Type}, {"Loan Officer", type text}, {"Loan Amount", Int64.Type}, {"Closing Date", type date}, {"GFE Application Date", type date}, {"Employee Referral Name", type text}, {"Past Client Name", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Borr SSN", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Borr SSN.1", "Borr SSN.2", "Borr SSN.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Borr SSN.1", Int64.Type}, {"Borr SSN.2", Int64.Type}, {"Borr SSN.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Borr SSN.1", type text}, {"Borr SSN.2", type text}, {"Borr SSN.3", type text}}, "en-US"),{"Borr SSN.1", "Borr SSN.2", "Borr SSN.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Borr SSN"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Merged Columns", "Co-Borr SSN", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Co-Borr SSN.1", "Co-Borr SSN.2", "Co-Borr SSN.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Co-Borr SSN.1", Int64.Type}, {"Co-Borr SSN.2", Int64.Type}, {"Co-Borr SSN.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"Co-Borr SSN.1", type text}, {"Co-Borr SSN.2", type text}, {"Co-Borr SSN.3", type text}}, "en-US"),{"Co-Borr SSN.1", "Co-Borr SSN.2", "Co-Borr SSN.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Co-Borr SSN"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns1", {"Borr SSN"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Employee Referral Name] = " ") and ([Past Client Name] = " ")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Co-Borrower Last Name", "Co-Borr SSN"})
in
    #"Removed Columns";

shared #"BI Customer File" = let
    Source = Excel.Workbook(File.Contents("C:\Users\actasson\Desktop\BI Customer File.xlsx"), null, true),
    #"BI Customer File_Sheet" = Source{[Item="BI Customer File",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"BI Customer File_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Bank Member SSN", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
    #"Removed Duplicates";

[KHorseman]

OK, so you have a relationship between these two tables? Do you have the filtering direction set to "Both" on that relationship, or "Single"? What are you trying to display in this visual exactly? Are you using a measure, a quick measure, or what?

[Anonymous]

Single.

I have 1 DB of SSNs of bank clients that I am trying to agrregate against a DB of Mortgage clients's SSNs to see how many Mortgage clients are also Bank clients.

I am not using any measured columns.

[KHorseman]

That doesn't really clarify what visual you're using and what data is going where in it.

[Anonymous]

my appologies - I am using a table visualization.

I am starting w the full list of banks client SSNs from DB1. The next column is from DB2 that has all the mortgage client's last names. Once those 2 columns are in place I filter out any blanks from the mortgage client's last name column as that returns a list of mortgage clients who are bank clients as well bc DB1 is linked to DB2 via SSN.

Adding any other fields from DB2 results in the error, mortgage loan number, mortgage loan amount, application date... etc.

[KHorseman]

So DB1 is just a table of client SSNs? 1 row per SSN? i.e. each SSN appears only once? And DB2 is a table of mortgages where each row has a reference to SSNs found in DB1, with a relationship between these two columns?

[Anonymous]

Yes - I removed the duplicates from each DB based on the SSN.

[KHorseman]

Add a measure

Total Amount = SUM(Encompass[Loand Amount])

and add that to the visual before adding any other columns. See if that makes any difference. Also try changing your visual to a matrix and put that measure under Values and see what that does.

[Anonymous]

When using the Table visualization - each column replicates what is input in the 1st row next to the Bank Member SSN field.

The filter function doesnt work correctly in the Matrix visual.

[Anonymous]

@Anonymous,

Could you please share sample data of your tables so that we can reproduce the scenario? And what output would you like to display?

Regards,
Lydia

[Anonymous]

My appologies - I dont have data sets I am able to share.

[Anonymous · Accepted solution]

I ended up creating a measure in the Bank DB for the mortgage loan total amount. That seemed to solution my problem of erroring out due to lack of memory. Adding fields from either table is snappier.

[KHorseman · Accepted solution]

That's what I suspected. When you just jam a bunch of columns together it isn't sure what to do with them. You need something to tell it how you want to aggreagate things and then it works smoothly. Honestly you're not really using Power BI until you start writing a few measures in my opinion.

[Anonymous]

Thanks again for all your assistance.