Performance Issue - Summarize - Please help

[Anonymous]

Hello PBIexperts,

I have factsales table with more than 1.5m rows and a factexchangerate table with more than 100k rows. 

See the below diagram, from which I took the logic for my model. (kudos to https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/ )

What my problem is. My calculations are been executed row by row, and this take a lot of time.

What I have done. I summarize the factsalestable per day and per currency and I recreate the realationships. It is working, but I'm losing all the other dimensions I use with slicers. 

What I'm trying to do is to decrease the amount of rows using variables and summarize. 

TotalSalesNew =

var summarizetable = SUMMARIZE(FactSales,FactSales[CURRENCY],FactSales[Date],"total_sales",SUM(FactSales[Sales]))

return

SUMX(summarizetable,[total_sales])*[TrnExchangeRate]

But it doesn't work. Can anyone help me out to solve this? 

[v-piga-msft]

Hi @Anonymous ,

What I'm trying to do is to decrease the amount of rows using variables and summarize. 

TotalSalesNew =

var summarizetable = SUMMARIZE(FactSales,FactSales[CURRENCY],FactSales[Date],"total_sales",SUM(FactSales[Sales]))

return

SUMX(summarizetable,[total_sales])*[TrnExchangeRate]

But it doesn't work. Can anyone help me out to solve this? 

---

Do you mean that the formula not show your expected output or still take a lot of time?

In addition, what is your data source and which connection do you use? Import, direct query or live connection?

Best  Regards,

Cherry

[Anonymous]

Hello @v-piga-msft ,

I'm using direct query. 

This part of the formula " SUMX(summarizetable,[total_sales]) " works just fine, but this part " [TrnExchangeRate] " not.

see below:

[v-piga-msft]

Hi @Anonymous ,

Sorry for the delay.

However, what is your desired output?

Best  Regards,

Cherry

[Anonymous]

@v-piga-msft 

https://1drv.ms/u/s!AuRtgr-nNJ5ChQs_yJC28vIvh5fh?e=5WsZM3

the link for the example pbi file