Crossjoin in PowerBI or SQL - Performance?

[vipett]

I have a report where I calculate the stock backwards (and projected forwards), for this I use a Cross join between my items table and calendar table.

However, it is quite a lot of data.. three years of history is needed and ~16000 articles, so in total 16 million rows..

When I do the cross join in PowerBI the performance in PowerBI desktop becomes really slow, If I want to add a new column, I have to wait ~30 seconds before typing and then 30-60 before I see the output..

Would it be smarter to do the cross join in the import? Of course the load will take much longer.

Or are there any other ways?

I need the Cartesian product unfortunately..

[bakesada]

@vipett ,
I have a similar situation and no access to SQL.
I have a report that is using import for all data sources.
To keep the dev experience moving smoothly, I use a data flow to complete the cross join and any calculated columns and import the result to Power BI Desktop.

This allows the cloud to "crunch the numbers" at 3 AM or whatever I just develop with the output of the cross join.

[vipett]

Hi, 

No not really..

I use a table with the Cartesian product of all dates and all items.

Then I link this to my transactions table (all movements, PO inbound, SO outbound, stock correction, etc.) Plus my forecast transcation tabel 

Based on this, I calculate what the stock was each day and what stock level I have estimated it to be each day going forward. 

[amitchandak]

@vipett , if you are doing this because you need to have data between the start date and the end date , refer

https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

You can generate function

even measures can do

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...