topic Help with Measure Performance in DAX Commands and Tips

Help with Measure Performance

Anonymous — Sat, 01 Jul 2023 05:21:57 GMT

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?

Re: Help with Measure Performance

tamerj1 — Sat, 01 Jul 2023 07:22:16 GMT

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.

Re: Help with Measure Performance

ppm1 — Sat, 01 Jul 2023 13:42:59 GMT

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

Re: Help with Measure Performance

Anonymous — Mon, 03 Jul 2023 05:30:25 GMT

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

Re: Help with Measure Performance

Anonymous — Mon, 03 Jul 2023 05:30:53 GMT

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