Which ways are more efficient comparing between having a Measure in DAX formula or using VAR?

[khacwee]

Dear all,

I have a performance related questions regarding which ways are more efficient and less resources consuming in Power BI for below?

Option 1: Having 3 Measures in total

Factor = calculate(sum(factor),filter(factortable,date>=min(timetable) && date<=max(timetable)))

Sales = calculate (sum(sales),filter(salestable,date>=min(timetable) && date<=max(timetable)))

Actual Sales = Sales * Factor

Option 2: Use only one measure, the other 2 measures will be VAR within the desired measure

Actual Sales =

var factor = calculate(sum(factor),filter(factortable,date>=min(timetable) && date<=max(timetable)))

var sales = calculate (sum(sales),filter(salestable,date>=min(timetable) && date<=max(timetable)))

return factor*sales

The resulting visuals will be a table with Branches as rows and Actual Sales as column:

Branches	Actual Sales
Branch A	10,000
Branch B	15,000
Branch C	20,000
Branch D	25,000

I have been wondering which way is more efficient as I am encountering limit per query issues in a complex visual.

Please help to advise, Thanks!

Wee

[Anonymous · Accepted solution]

Hi @khacwee ,

Regarding your question, both options are valid and efficient. However, the second option is more efficient in terms of performance because it uses only one measure instead of three measures.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[sergej_og]

It seems to me "Option 1" is good to go in this case.
You can use every single measure (once created) over and over again for other purposes in your business logic...maybe tweek a bit better for performance issues.
As @some_bih mentioned using FILTER might be not best practice.
Try to avoid if you run into performance issues.

Regards

[some_bih]

Hi @khacwee seems that you use filter filter(salestable,date in both option. This is not best practice as salestable is big table (many rows). Think about changing this part, as we assumy that factor table is very little one compared with salestable.

Hope this help, kudos appreciated.