Summarizecolumns multiple tables (for export to excel)

[mvhoffe]

Hello,

I have a challenge that I've been puzzling with for a long time. I would like your advice on this.

I have a dataset containing a large number of tables that I also use for my visuals.

My employer would like to have an overview in Excel of all sales per relation, location, product once a month.

Something like this:

My data model looks like this:

So far nothing special.

Ideally, you create a visual/matrix for the table to be exported to excel and export it. Only because of the many lines and columns this is not possible and it gives an error.

Second solution would be to use a caculatetable in combination with summarizecolumns (CALCULATETABLE ( SUMMARIZECOLUMNS (........ ) only then I can only summarize from 1 table.

A third solution I tried was to merge all the tables and use them for a calculatetable.
The problem is that it then becomes very, very slow. As a result, refreshing data takes between 1 to 2 hours.
I'm talking about about 2 million invoice lines.

Thanks in advance for your help.

[smpa01]

4th option if you are on premium workspace and access to Power automate (default ENV will do, you don't need premium) , you can called DAX API by following this video from Ruth which will loop though all the lines and bypass 100k limit to generate the 2mil...

Once the flow runs you can pick up the pieces from the folder and sew them to a CSV (I hope I am right).  

@mahoneypat2

@mvhoffe  BTW excel has limitation of excel limitation of 1,048,576 rows by 16,384 columns, So you can't filt 2mil+ row in an excel.

[mvhoffe]

sorry @smpa01 .
This is a nice solution to export 1 table to csv/excel. That is not the problem (i can do this also with Dax studio ?). 

The problem is how to merge the different fields from the 4 tables without big performance loss. And then export this to excel/csv.

[smpa01 · Accepted solution]

The problem is how to merge the different fields from the 4 tables without big performance loss - make sure you have a datamodel that speaks to that business req, optimize dax measures and you should be good.

[mvhoffe]

@smpa01 thanks for reply,

I am going to watch the video and try the solution.

I know that excel has a limitation of 1,048,076 rows, but because i am summarize by product (and client) i only because I stay far below the 1 million lines, fortunately I don't suffer from that.