Forum Discussion
Native Query brings all data in tabular visual when currency conversion is applied.
All,
I wanted to inform that the issue was resolved.
A Date bridge table containing column with the first of month was added.
This date bridge table was joined to the Exchange Rate Table via a many-to-one join.
The date bridge table is then joined to the fact table via a one to many join.
The DAX query was then updated accordingly, and this resolved the issue.
grazitti_sapna
I tested the two measures. The currency conversion logic is working as such.
But, when I add an ID (lowest level of granularity of the table), I see that the power bi sends the same error in the table visual -
Error fetching data for this visual. The resultset of a query to external dataset has exceeeded the maximum allowed size of 1million rows.
So, now my question is -
if we keep the monthly conversion logic as is, then do you think it would make sense to break the many-to-many join between the fact table and the monthly exchange rate table?
Update - I broke the many to many join between the monthly exchnage rate and the fact table by including two bridge tables. So now the joins are all one-to-many.
But inspite of the making this change, and adding the conversion logic for monthly aggregation, I am still seeing the 1 million records issue with the table visual when the ID column is included in.
HI Rdarshana,
Power BI's external dataset query limit = 1 million rows
When you use DirectQuery, and include the ID (lowest granularity) in your visual, Power BI generates a query that returns one row per ID per date (or per measure). This can easily exceed 1 million rows, especially if:
-
You have many IDs (e.g., transactions, invoice lines, etc.)
-
You combine that with date or currency dimensions
-
Even if you're aggregating monthly, including
IDin the visual disables pre-aggregation optimization
Below are few suggestions
1. Avoid Using
IDin Visuals Unless Necessary-
The root of the 1M row overflow is not in the measure logic anymore — it's the visual trying to retrieve too many rows.
-
Try to summarize the data before adding
IDto the visual. -
If you must show
ID, implement pagination, filters, or drill-through.
2. Use Aggregated Tables
If your model allows it, materialize pre-aggregated tables (e.g., monthly totals per currency per ID or product), then use those tables in visuals:
-
Create a new aggregated table in Power BI using DAX or in the source DB:
AggregatedFact =
SUMMARIZE(
'Fact Table',
'Fact Table'[ID],
'Calendar'[Month],
'Fact Table'[Currency Code],
"TotalMeasure", SUM('Fact Table'[Measure1])
)-
Then, apply currency conversion on top of this pre-aggregated table.
3. Use Drill-through Instead of One Big Table
Rather than allowing a flat table at full granularity, design your report as:
-
Overview page: summary by month or product
-
Drill-through page: show
ID-level detail only when context is selected
Hi grazitti_sapna
The tabular visual is part of a drill-through page.
We have a Date slicer and Currency Slicer on the parent page. The DimensionA acts as an attribute to drill-through to the detailed level table.
So, when the drill-through report displays only 2 records for a given date slicer, and a given dimension A attribute, the currency conversion logic should ideally only convert the data for the two records. So, it is confusing when Power BI displays 1M+ records data issue.
If we were to look at the data model and many-to-many joins, even after converting the many-to-many joins to one-to-many (inner joins), we are seeing the same issue.
So, if the original query is pulling in only one ID in the tabular visual, then the conversion logic should ideally work in this case.
Is there anything else that is missing in being tested out?
-
