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.
Highlighted
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

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,288)