Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Performance optimization of DAX

Hi All,

 

I have a sample DAX formula as below. The problem with this DAX is it will calculate for all the variables one by one (6 variables in total). Then based on the IF condition it will return the result of only one variable. 

 

Please help in suggesting a way in which based on the IF condition, only one set of variables (3 variables) should be executed. Either First Set or Second Set should be executed. This will help to reduce Performance issues.

 

-- First Set --

var _Tbl1_Col1 = calculate(sum(table1[Column1]))
var _Tbl1_Col2 = calculate(sum(table1[Column2]))
var result_Tbl1 = _Tbl1_Col2 - _Tbl1_Col1

 

--Second Set--

var _Tbl2_Col1 = calculate(sum(table2[Column1]))
var _Tbl2_Col2 = calculate(sum(table2[Column2]))
var result_Tbl2 = _Tbl2_Col2 - _Tbl2_Col1

 

-- Return Result --

var if(variable = "Table1", result_Tbl1 ,result_Tbl2)

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Are you using calculated columns or measures? A measure will perform better than a calculated column.

    Refer this:Calculated Columns and Measures in DAX - SQLBI

    Or you can try reducing the variables.

    var result_Tbl1 = calculate(sum(table1[Column2]))-calculate(sum(table1[Column1]))
 
var result_Tbl2 = calculate(sum(table2[Column2]))-calculate(sum(table2[Column1]))

return if(variable = "Table1", result_Tbl1 ,result_Tbl2)

    Or you can create measure1 and measure 2 separately, and then create the resulting measure 3.

    measure3 =if(variable="Table1",measure1,measure2)

     

    Best Regards,

    Neeko Tang

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