Help with Measure Performance

[Anonymous]

Hi

I have a very simple measure to calculate amount using forex rate but it is running slow once I include fields from entities like Contract and Org. The measure is used in a martix. When used in table at invoice level from fact without any filters it still runs fine but with inclusion of additional fields from Contract and Org slows it down. 

The measure formula is as follows

Outstanding_Amount_FXInc =

var _selectedPeriod = MAX('FactInvoices'[Reporting_Date])

VAR _financialPeriod = FORMAT(_selectedPeriod,"MMM") & " " & FORMAT(_selectedPeriod,"YYYY")

var fxRate = CALCULATE(MAX(FxRate[Value]),FxRate[MonthYear] = _financialPeriod)

RETURN SUM('FactInvoices[Outstanding_Amount]) * IF(MAX('Company'[Company_Code]) = "x",1/fxRate,1)
Model is as below with counts mentioned. Any idea how to fix the measure to perform better?

[ppm1]

You should add a Date table to your model (that has a financial period column in it) and relate that to your Reporting Date column in the fact table. You could also likely merge your rate table into your Date table, so you could then do a simple RELATED from the fact table to get the rate for that date.

What are the relationships between your tables? I suspect you have a bi-directional relationship in the mix (to the Company table?). Your model should have only 1:M relationships if at all possible. Can you grab the Company code with a RELATED too, to avoid the IF?

Pat

[Anonymous]

Thanks. I updated the model with relationship between Date and FxRate/ Date & Fact and using related function. It works better now.

[tamerj1 · Accepted solution]

Hi @Anonymous 
It is would be a good idea to have a date table that filters both Fact and FxRate tables. That would definitely improve the performance. Also note that you DAX would probably generate wrong total.

[Anonymous]

Thanks. I updated the model with relationship changes as per suggestion and using related function. It works better now.