Exporting 10 million rows to CSV

Hi Koritala 

You can use DAX Studio to do that. Please note though that the number of rows you can export depends on your device's memory. https://www.youtube.com/watch?v=op6f-3uUFYg

Koritala 

In this scenario, you can use the table visual dax query expression and run it in the dax studio, you might need to tweak the dax query a bit to achive the desiged result. I will explain the steps. 

1. Disable 'Totals' for the table visual

2. Copy the table visual's dax query from performance analyzer

3. Open DAX Studio and paste the dax query, for example in my case the query is this 

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"Furniture"}, 'sales_rawtransactions'[product_category])

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'sales_rawtransactions'[product_category],
			'sales_rawtransactions'[product_name],
			'sales_rawtransactions'[customer_email],
			__DS0FilterTable,
			"Sumquantity_sold", CALCULATE(SUM('sales_rawtransactions'[quantity_sold])),
			"Sumproduct_price", CALCULATE(SUM('sales_rawtransactions'[product_price]))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			501,
			__DS0Core,
			'sales_rawtransactions'[product_category],
			1,
			'sales_rawtransactions'[product_name],
			1,
			'sales_rawtransactions'[customer_email],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'sales_rawtransactions'[product_category],
	'sales_rawtransactions'[product_name],
	'sales_rawtransactions'[customer_email]

Now you need to make some changes to the code 

4. Remove "Order By clause" it is not required

5. By default, power bi restricts the number of rows visible in the table visual to 501, You can remove that part from the dax expression and Evaluate the immediate previous table expression, in my case the expression will become 

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"Furniture"}, 'sales_rawtransactions'[product_category])

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'sales_rawtransactions'[product_category],
			'sales_rawtransactions'[product_name],
			'sales_rawtransactions'[customer_email],
			__DS0FilterTable,
			"Sumquantity_sold", CALCULATE(SUM('sales_rawtransactions'[quantity_sold])),
			"Sumproduct_price", CALCULATE(SUM('sales_rawtransactions'[product_price]))
		)


EVALUATE
	__DS0Core

6. Run the code in dax studio just to verify whether it is giving all the required number of rows as result 

You can check the row count in the right bottom of dax studio

7. Now in dax studio, change the "Results" to "File" and run the code again

Thats it, a csv file will be created in your local.

Hope this helps

Connect on LinkedIn

You can read my blogs here: https://www.techietips.co.in/

Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!