Forum Discussion

eddd83's avatar
eddd83
Icon for Resolver I rankResolver I
2 years ago

How find the most computation expensive calculated columns

So in the data model that I inherited, there are a number of calculated columns in the main table. By chance, I noticed if I delete this particular "end customer" column, it would cut the time to ope...
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

I am surprised that the first query performs worse than the second. What do the engine timings look like for both?

eddd83's avatar
eddd83
Icon for Resolver I rankResolver I
2 years ago

By engine timings, did you mean server timings? Again, the top one (end_customer) is the CPU intensive calcuation. 

 

I'm not 100% sure i did this correctly, but all i did was drop each calculated column into a table (a seperate table for each column). Did a "refresh visuals" then a "copy query". Pasted the DAX into DAX studio and hit "run" with "query plan" and "server timings" turned on.

 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Yes, that is the correct process.  But you are supposed to evaluate the DAX for the calculated column, not the final result.

     

    Rather intriguing that both queries spend all their time in the formula engine.  A simple column pull should be done entirely in the storage engine.

    • eddd83's avatar
      eddd83
      Icon for Resolver I rankResolver I
      2 years ago

      but how do i evaluate the dax for the column in dax studio?

       

      End_Customer =
VAR customer_number = [Customer Number]
VAR int_sales_brid_0_ship_to_num =
    RELATED ( Internal_Sales_Bridge[Ship_To_Number] )
VAR lift_table_brid_0_cus_num =
    RELATED ( Lift_Table_Bridge[Customer Number] )
VAR output =
    IF (
        ISBLANK ( int_sales_brid_0_ship_to_num ),
        IF (
            ISBLANK ( lift_table_brid_0_cus_num ),
            "customer_number",
            "lift_table_brid_0_cus_num"
        ),
        "int_sales_brid_0_ship_to_num"
    )
RETURN
    output

       

      For example, how do I put the above into DAX studio? (FYI, this a short version of a similar formula from a related model)

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        Everything in DAX is a table.  You can use 

         

        EVALUATE ROW("column name", <your DAX>)

         

        or 

        EVALUATE  {<your DAX>}

         

        or any other variant that produces a table.

         

        Like so:

        EVALUATE{
VAR customer_number = [Customer Number]
VAR int_sales_brid_0_ship_to_num =
    RELATED ( Internal_Sales_Bridge[Ship_To_Number] )
VAR lift_table_brid_0_cus_num =
    RELATED ( Lift_Table_Bridge[Customer Number] )
RETURN
    IF (
        ISBLANK ( int_sales_brid_0_ship_to_num ),
        IF (
            ISBLANK ( lift_table_brid_0_cus_num ),
            "customer_number",
            "lift_table_brid_0_cus_num"
        ),
        "int_sales_brid_0_ship_to_num"
    )
}