cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdemos02 Frequent Visitor
Frequent Visitor

Performance Issue - Summarize - Please help

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? 
 

Screenshot_1.png

4 REPLIES 4
Community Support Team
Community Support Team

Re: Performance Issue - Summarize - Please help

Hi @mdemos02 ,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mdemos02 Frequent Visitor
Frequent Visitor

Re: Performance Issue - Summarize - Please help

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:

Screenshot_2.png

Highlighted
mdemos02 Frequent Visitor
Frequent Visitor

Re: Performance Issue - Summarize - Please help

Community Support Team
Community Support Team

Re: Performance Issue - Summarize - Please help

Hi @mdemos02 ,

Sorry for the delay.

However, what is your desired output?

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)