Forum Discussion

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper V
8 months ago
Solved

Surprisingly slow measure

Hi. Looking for some advice to speed up this measure which is surprsingly slow   FTEs Purchased new = AVERAGEX(ADDCOLUMNS(SUMMARIZE(DateTable,DateTable[MonthYear]), ...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    8 months ago

    Hi mike_asplin ,

    Thanks for sharing the PBIX file.

    Please try the following version of the measure. It keeps the same calculation logic as your original approach but removes the use of SUMMARIZE and ADDCOLUMNS, which are contributing to the performance issue:

    AVERAGEX (
    VALUES ( DateTable[MonthYear] ),
    SUMX (
        VALUES ( Client[Client ID] ),
        CALCULATE (
            MAX ( Client[FTE Prev 30 days] )
                * [Distinct Paying Clients ex Canc]
        )
    )
)

    This also avoids the “single value cannot be determined” error by explicitly resolving the client level FTE value.

     

    Attached pbix file for reference.

    Hope this helps.
    Please reach out for further assistance.
    Thank you.

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper V
8 months ago

Sorry took a while to cut down the size of the data

 

Under Metrics/FTE are my previous attempts

 

[total FTE Purchased] calculates on the fly for every month but it is only marginally slower than

 

[FTEs Puchased new] which calcuates the last 30 days FTE as a column on client table and uses that for every month. Less accurate, but i thought it would be much quicker. 

 

[FTE purchased Optimized]is stuck up in ABV folder as cant move it 

 

File is here   https://www.dropbox.com/scl/fi/z3ml56s8habcc2k4q9py0/Trinity-PowerBI-test.pbix?rlkey=2q2qnqy4qliyofyv8qbz72av7&dl=0

 

Thanks for any advice

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi mike_asplin ,

    Thanks for sharing the PBIX file.

    Please try the following version of the measure. It keeps the same calculation logic as your original approach but removes the use of SUMMARIZE and ADDCOLUMNS, which are contributing to the performance issue:

    AVERAGEX (
    VALUES ( DateTable[MonthYear] ),
    SUMX (
        VALUES ( Client[Client ID] ),
        CALCULATE (
            MAX ( Client[FTE Prev 30 days] )
                * [Distinct Paying Clients ex Canc]
        )
    )
)

    This also avoids the “single value cannot be determined” error by explicitly resolving the client level FTE value.

     

    Attached pbix file for reference.

    Hope this helps.
    Please reach out for further assistance.
    Thank you.

    • v-veshwara-msft's avatar
      v-veshwara-msft
      Icon for Community Support rankCommunity Support
      7 months ago

      Hi mike_asplin ,
      Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
      Thank you.

    • mike_asplin's avatar
      mike_asplin
      Icon for Helper V rankHelper V
      7 months ago

      Sorry been on holiday for 2 weeks

       

      I did a comparison between my old code

      AVERAGEX(ADDCOLUMNS(SUMMARIZE(DateTable,DateTable[MonthYear]),
                                      "FTE",SUMX(SUMMARIZE(Client,Client[Client ID],Client[FTE Prev 30 days]),
                                                 Client[FTE Prev 30 days]*[Distinct Paying Clients ex Canc])),
                                       [FTE])

       

      and your code

      AVERAGEX (
    VALUES ( DateTable[MonthYear] ),
    SUMX (
        VALUES ( Client[Client ID] ),
        CALCULATE (
            MAX ( Client[FTE Prev 30 days] )
                * [Distinct Paying Clients ex Canc]
        )
    )
)

       

      times are virtually identical so this hasnt speeded it up at all? 

       

      In my full model both take 26,000 ms for just 3 months. to do the whole year is 200,000ms!. Its much quicker in the test model as there are only 76,000 booking rows not the 440,000 in the full model.  Seems a huge amount of time for what seems like a simple calculation? 

      • v-veshwara-msft's avatar
        v-veshwara-msft
        Icon for Community Support rankCommunity Support
        7 months ago

        Hi mike_asplin ,

         

        Thanks for running the comparison and for sharing the timings.

        I tested this on my side as well, and the behaviour matches what you are seeing. Although the rewritten measure removes SUMMARIZE and ADDCOLUMNS, it does not change the main cost of the calculation. In both versions, the measure still iterates clients per month and evaluates the distinct paying clients logic against the Bookings table. With around 440k booking rows, that part dominates the execution time, so the overall duration remains almost the same.

        This also explains why the test model is much faster. Reducing the number of booking rows has a direct impact, whereas changes to the DAX structure alone do not.

         

        A meaningful performance improvement would require changing where the calculation is done, for example by pre aggregating bookings at month and client level, or moving that logic to Power Query or the source, and then basing the measure on that smaller dataset. Without that kind of model change, the performance you are seeing is broadly consistent with this calculation pattern and data volume.

         

        Hope this helps. Please reach out for further assistance.
        Thank you.