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
eddd83's avatar
eddd83
Icon for Resolver I rankResolver I
2 years ago

So i did what you suggested. Here's the problematic calculated column (end_customer).

 

However, here's another column (transaction_id_order). Removing this from the data model makes almost no difference in speed. Whereas removing end_customer makes a big difference. Again, i see nothing different between the 2 query plans aside from the problematic end_customer having fewer records.

 

Here's what the end_customer logic looks like. Basically it's checking the related(XXX) for a number of tables and returns the first one that isn't blank.

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)