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.
Solved! Go to 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.
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.
Proud to be a Super User!
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?
Proud to be a Super User!
'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";
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?
Proud to be a Super User!
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.
That doesn't really clarify what visual you're using and what data is going where in it.
Proud to be a Super User!
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.
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?
Proud to be a Super User!
Yes - I removed the duplicates from each DB based on the SSN.
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.
Proud to be a Super User!
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.
@AdamTass1,
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
My appologies - I dont have data sets I am able to share.
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.
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.
Proud to be a Super User!
Thanks again for all your assistance.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |