Maximum allowed size of 1000000 rows

[PriyankaA905]

Hi,

I have to prepare the following Lag report

In above case, in January 2022,13413 number of accounts opened. In following months there is a count of A/cs which are not closed for eg As on 28 feb 22, 3 accounts were closed so the no. of accounts active are 13410. To acheive this I have 3 measures

Account opened in Cust Dim =

Var monthafter = SELECTEDVALUE(Series[Series])

Var Currentfirstopeningmonth =SELECTEDVALUE('Customer Detail'[Lag_opening date])

return

CALCULATE(

DISTINCTCOUNT('Customer Detail'[ACNTS_INTERNAL_ACNUM]),

FILTER('Customer Detail',

EOMONTH('Customer Detail'[ACNTS_OPENING_DATE],0)=EOMONTH(Currentfirstopeningmonth,monthafter)

)

)

Account closed in Cust dim =

var as_on_date = SELECTEDVALUE(Date_DIM[AS_ON_DATE])

return

CALCULATE(

DISTINCTCOUNT('Customer Detail'[ACNTS_INTERNAL_ACNUM]),'Customer Detail'[ACNTS_CLOSURE_DATE]<>BLANK(),

FILTER('Customer Detail',

EOMONTH('Customer Detail'[ACNTS_CLOSURE_DATE],0)<=EOMONTH(as_on_date,0)

))

lag reort=

Var A = SELECTEDVALUE(Date_DIM[AS_ON_DATE])

return

CALCULATE([Account opened in Cust Dim]-[Account closed in Cust dim],

filter(

'Customer Detail',

SELECTEDVALUE('Customer Detail'[Lag_opening date])<=A))

In Matrix table

Rows= Account Opening Month and Year (Customer Dim)
Columns = As on Date (Date Dim Table)

Values = Lag Report measure

In above case,I have filtered only one product code hence the measure is running properly but when I use all the products it is giving the following error

How can I solve this error?

[v-henryk-mstf · Accepted solution]

Hi @PriyankaA905 ,

The reason for such error messages is that Power BI creates queries as efficiently as possible. However, in some cases, the generated queries may not be efficient enough to avoid refresh failures. One such case is when the generated query retrieves too many rows (more than 1 million rows) from the back-end data source, the following error will occur.

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

related document link.

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PriyankaA905]

I am attaching sample Data here:

Customer Dim:

ACNTS_INTERNAL_ACNUM	ACNTS_OPENING_DATE	ACNTS_CLOSURE_DATE	Lag_opening date
1	1-Jan-22		Jan-22
2	1-Jan-22		Jan-22
3	3-Jan-22		Jan-22
4	3-Jan-22		Jan-22
5	3-Jan-22	18-Jan-22	Jan-22
6	3-Jan-22	2-Mar-22	Jan-22
7	3-Jan-22	7-Mar-22	Jan-22
8	3-Jan-22	16-Mar-22	Jan-22
9	3-Jan-22	13-Apr-22	Jan-22
10	4-Jan-22		Jan-22
11	4-Jan-22		Jan-22
12	4-Jan-22	15-Jan-22	Jan-22
13	4-Jan-22	8-Mar-22	Jan-22
14	4-Jan-22	12-Apr-22	Jan-22
16	5-Jan-22		Jan-22
17	6-Jan-22		Jan-22
18	6-Jan-22		Jan-22
20	7-Jan-22		Jan-22
21	8-Jan-22		Jan-22
22	8-Jan-22		Jan-22
23	10-Jan-22		Jan-22
25	10-Jan-22	11-Mar-22	Jan-22
26	11-Jan-22		Jan-22
28	12-Jan-22		Jan-22
29	12-Jan-22		Jan-22
31	12-Jan-22	28-Jan-22	Jan-22
32	12-Jan-22	16-Mar-22	Jan-22
33	12-Jan-22	27-Apr-22	Jan-22
34	13-Jan-22		Jan-22
36	14-Jan-22		Jan-22
37	14-Jan-22		Jan-22
39	15-Jan-22		Jan-22
40	16-Jan-22		Jan-22
41	16-Jan-22		Jan-22
42	17-Jan-22		Jan-22
44	18-Jan-22		Jan-22
45	18-Jan-22		Jan-22
46	18-Jan-22	24-Mar-22	Jan-22
47	19-Jan-22		Jan-22
48	19-Jan-22		Jan-22
50	20-Jan-22		Jan-22
51	21-Jan-22		Jan-22
53	21-Jan-22	7-Apr-22	Jan-22
54	22-Jan-22		Jan-22
56	24-Jan-22		Jan-22
58	24-Jan-22	2-Feb-22	Jan-22
59	25-Jan-22		Jan-22
60	25-Jan-22		Jan-22
61	25-Jan-22	2-May-22	Jan-22
62	26-Jan-22		Jan-22
63	26-Jan-22		Jan-22
64	27-Jan-22		Jan-22
65	27-Jan-22		Jan-22
66	27-Jan-22	27-Jan-22	Jan-22
67	28-Jan-22		Jan-22
68	28-Jan-22		Jan-22
69	28-Jan-22	30-Apr-22	Jan-22
70	29-Jan-22		Jan-22
71	29-Jan-22		Jan-22
72	29-Jan-22	11-Feb-22	Jan-22
73	29-Jan-22	25-Feb-22	Jan-22
74	29-Jan-22	8-Apr-22	Jan-22
75	30-Jan-22		Jan-22
76	30-Jan-22		Jan-22
77	31-Jan-22		Jan-22
78	31-Jan-22		Jan-22
80	1-Feb-22		Feb-22
81	1-Feb-22	11-Feb-22	Feb-22
82	1-Feb-22	21-Mar-22	Feb-22
83	1-Feb-22	13-Apr-22	Feb-22
84	2-Feb-22		Feb-22
85	2-Feb-22		Feb-22
86	3-Feb-22		Feb-22
87	3-Feb-22		Feb-22
89	4-Feb-22		Feb-22
90	4-Feb-22	5-Mar-22	Feb-22
91	4-Feb-22	31-Mar-22	Feb-22
92	4-Feb-22	4-May-22	Feb-22
93	5-Feb-22		Feb-22
94	5-Feb-22		Feb-22
97	6-Feb-22		Feb-22
98	7-Feb-22		Feb-22
99	7-Feb-22		Feb-22
100	7-Feb-22	7-Feb-22	Feb-22
101	8-Feb-22		Feb-22
102	8-Feb-22		Feb-22
103	8-Feb-22	2-Mar-22	Feb-22
104	8-Feb-22	3-Mar-22	Feb-22
105	9-Feb-22		Feb-22
106	9-Feb-22		Feb-22
107	10-Feb-22		Feb-22
108	10-Feb-22		Feb-22
109	11-Feb-22		Feb-22
110	11-Feb-22		Feb-22
111	11-Feb-22	21-Mar-22	Feb-22
112	12-Feb-22		Feb-22
113	12-Feb-22		Feb-22
114	14-Feb-22		Feb-22
115	14-Feb-22		Feb-22
116	15-Feb-22		Feb-22
117	15-Feb-22		Feb-22
119	15-Feb-22	30-Apr-22	Feb-22
120	16-Feb-22		Feb-22
121	16-Feb-22		Feb-22
122	16-Feb-22	25-Apr-22	Feb-22
123	17-Feb-22		Feb-22
124	17-Feb-22		Feb-22
125	17-Feb-22	12-Apr-22	Feb-22
126	18-Feb-22		Feb-22
127	18-Feb-22		Feb-22
128	18-Feb-22	30-Mar-22	Feb-22
129	19-Feb-22		Feb-22
130	19-Feb-22		Feb-22
131	20-Feb-22		Feb-22
132	20-Feb-22		Feb-22
133	21-Feb-22		Feb-22
134	21-Feb-22		Feb-22
135	22-Feb-22		Feb-22
136	22-Feb-22		Feb-22
137	23-Feb-22		Feb-22
138	23-Feb-22		Feb-22
139	24-Feb-22		Feb-22
140	24-Feb-22		Feb-22
141	24-Feb-22	18-Apr-22	Feb-22
142	24-Feb-22	4-May-22	Feb-22
143	25-Feb-22		Feb-22
144	25-Feb-22		Feb-22
145	26-Feb-22		Feb-22
146	26-Feb-22		Feb-22
147	28-Feb-22		Feb-22
148	28-Feb-22		Feb-22
149	28-Feb-22	8-Apr-22	Feb-22
150	28-Feb-22	28-Apr-22	Feb-22
151	1-Mar-22		Mar-22
152	1-Mar-22		Mar-22
153	2-Mar-22		Mar-22
154	2-Mar-22		Mar-22
155	3-Mar-22		Mar-22
156	3-Mar-22		Mar-22
157	3-Mar-22	8-Apr-22	Mar-22
158	4-Mar-22		Mar-22
159	4-Mar-22		Mar-22
160	5-Mar-22		Mar-22
161	5-Mar-22		Mar-22
162	5-Mar-22	23-Mar-22	Mar-22
163	6-Mar-22		Mar-22
164	6-Mar-22		Mar-22
165	7-Mar-22		Mar-22
166	7-Mar-22		Mar-22
167	7-Mar-22	16-Mar-22	Mar-22
168	8-Mar-22		Mar-22
169	8-Mar-22		Mar-22
170	8-Mar-22	11-Apr-22	Mar-22
171	9-Mar-22		Mar-22
172	9-Mar-22		Mar-22
173	9-Mar-22	26-Apr-22	Mar-22
174	10-Mar-22		Mar-22
175	10-Mar-22		Mar-22
176	10-Mar-22	16-Apr-22	Mar-22
177	11-Mar-22		Mar-22
178	11-Mar-22		Mar-22
179	11-Mar-22	2-Apr-22	Mar-22
180	12-Mar-22		Mar-22
181	12-Mar-22		Mar-22
182	12-Mar-22	16-Apr-22	Mar-22
183	12-Mar-22	4-May-22	Mar-22
184	13-Mar-22		Mar-22
185	14-Mar-22		Mar-22
186	14-Mar-22		Mar-22
187	14-Mar-22	16-Mar-22	Mar-22
188	15-Mar-22		Mar-22
189	15-Mar-22		Mar-22
190	16-Mar-22		Mar-22
191	16-Mar-22		Mar-22
192	17-Mar-22		Mar-22
193	17-Mar-22		Mar-22
194	17-Mar-22	13-Apr-22	Mar-22
195	18-Mar-22		Mar-22
196	18-Mar-22		Mar-22
197	19-Mar-22		Mar-22
198	19-Mar-22		Mar-22
199	21-Mar-22		Mar-22
200	21-Mar-22		Mar-22
201	22-Mar-22		Mar-22
202	22-Mar-22		Mar-22
203	22-Mar-22	22-Mar-22	Mar-22
204	23-Mar-22		Mar-22
205	23-Mar-22		Mar-22
206	23-Mar-22	23-Mar-22	Mar-22
207	23-Mar-22	28-Mar-22	Mar-22
208	23-Mar-22	5-Apr-22	Mar-22
209	24-Mar-22		Mar-22
210	24-Mar-22		Mar-22
211	24-Mar-22	21-Apr-22	Mar-22
212	25-Mar-22		Mar-22
213	25-Mar-22		Mar-22
214	26-Mar-22		Mar-22
215	26-Mar-22		Mar-22
216	26-Mar-22	4-May-22	Mar-22
217	27-Mar-22		Mar-22
218	27-Mar-22		Mar-22
219	28-Mar-22		Mar-22
220	28-Mar-22		Mar-22
221	29-Mar-22		Mar-22
222	29-Mar-22		Mar-22
223	29-Mar-22	16-Apr-22	Mar-22
224	29-Mar-22	27-Apr-22	Mar-22
225	30-Mar-22		Mar-22
226	30-Mar-22		Mar-22
227	31-Mar-22		Mar-22
228	31-Mar-22		Mar-22

Date Dim:

AS_ON_DATE

31-Jan-22

28-Feb-22

31-Mar-22

30-Apr-22