Forum Discussion

khacwee's avatar
khacwee
Frequent Visitor
3 years ago
Solved

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

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:

 

BranchesActual Sales
Branch A10,000
Branch B15,000
Branch C20,000
Branch D25,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's avatar
    Anonymous
    3 years ago

    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.           

3 Replies

  • some_bih's avatar
    some_bih
    Community Champion

    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.

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.