The resultset of a query to external data source has exceeded the maximum allowed size of '1000000'

[user_dil]

Hi All,

I have created a Power BI report connecting to a fact table using the direct query. The tables contain data from 2019. For the purpose of my visuals, I have filtered the table using the below M query.

= Table.SelectRows(ft_transactions, each Date.IsInCurrentMonth( [inv_date] ) or Date.IsInPreviousNMonths([inv_date], 2) )

Then I created a calculated table using the below dax, for the underline fact table For the purpose of improving the report performance.

Current month Sales=
SUMMARIZE (
ALL ( 'ft_transactions'[define date],'ft_transactions'[product_code],'ft_transactions'[product_category],'ft_transactions'[prd_desc]),
'ft_transactions'[define date], 'ft_transactions'[product_code],'ft_transactions'[product_category],'ft_transactions'[prd_desc],
"Total Sales Value", SUMX('t_transactions',[actual ach] ),
"Total Sales Volume", SUM('t_transactions'[actual vol]))

** define date is a calculater col that gives first date of each month.

But the issue is, I'm getting this error as the row count exceeds 1M for the underline table.

 The resultset of a query to external data source has exceeded the maximum allowed size of '1000000'
Can anyone suggest a work around to overcome this issue.

[amitchandak]

@user_dil , Are you using direct query mode ?

Also for current month previous month sue time intelligence measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

or measure like

MTD Today =
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

LMTD Today=
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

LYMTD Today =
var _min = eomonth(today(),-13)+1
var _max1 = today()-1
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

MTD Yesterday =
var _max = today() -1
var _min = eomonth(_max,-1)+1
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Last Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[user_dil]

yes, but creating measures directly from the fact table makes the visuals loading slow. That's the reason I used SUMMARIZE function to create calculated table.