Forum Discussion

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper V
9 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.

Jai-Rathinavel's avatar
Jai-Rathinavel
Icon for Super User rankSuper User
9 months ago

mike_asplin Can you try the below DAX and see if it helps. ?

FTEs Purchased 1 =
AVERAGEX(
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            DateTable[MonthYear],
            Client[Client ID],
            "FTE Contribution", Client[FTE Prev 30 days] * [Distinct Paying Clients ex Canc]
        ),
        "FTE", [FTE Contribution]
    ),
    [FTE])

 

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

    Unfortunately its errors A single value of [FTE Prev 30 days] in table clients cant be found. 

     

    There are 3 dates

    Datetable

    Booking in which the date of the booking is related to the datetable

    Clients in which the client ID is related to the bookings.

     

    I am calculating essential the number of Employees each client needed each day (sometimes 1 or might be 2) made in prev 30 days and storing it as a column on the client table.

     

    I then need to calculate how many employees were needed in the month by summing the product of the column for each client that was active in the month and repeat that for previous months. Hence the SUMX over clients and the AverageX over the Monthyear.  

     

    Can you use summarizecolumns when there is no relationship between Datetable and clients? I guess not. 

     

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      9 months ago

      Hi,

      Share some dummy data to work with.  Show the expected result.