none
Count Distinct Calculated Column over Related Table

    Question

  • I have the following PowerPivot model:

    CompanyName -<  Users -< Usage >- Date

    There is also inactive relationship:

    CompanyName -< Usage (inactive)

    I would like have a calculated column that would a distinct count number of each user within each company. This is so I can use it in a calculation within the usage table.

    I have try the following: = COUNTX(RELATEDTABLE(Users), DISTINCT( Users[ContactID]))

    But I get the following error:

    Memory error: Allocation failure : Not enough storage is available to process this command. . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine. Is there a better way to do the calculation?

    Regards

    Steven Wright

    Wednesday, July 03, 2013 2:31 PM

Answers

  • That *IS* a tricky one.  Ideally there'd be a DISTINCTCOUNTX function :)

    I just wrote the following formula in one of my models:

    =CALCULATE(DISTINCTCOUNT(Users[Contact ID]), FILTER(Users, Users[CompanyID]=CompanyName[CompanyID]))

    I don't know the names of the right columns in your model, but the two columns I use in the FILTER should be the columns that form the relationship between the two tables.

    Let me know on twitter if this works or not, as I don't get notified of new posts as they happen here.

    • Proposed as answer by Rob CollieMVP Thursday, July 04, 2013 11:25 AM
    • Marked as answer by Steve J Wright Thursday, July 04, 2013 11:46 AM
    Thursday, July 04, 2013 11:15 AM

All replies

  • This new calc column you are writing - is it in the Users table or the Usage table or the CompanyName table?

    Wednesday, July 03, 2013 8:23 PM
  • The new calc column was for the CompanyName, so that I can use slice my data against.
    Thursday, July 04, 2013 7:15 AM
  • OK, and in the Users[Contact ID] column, there are duplicates?  The same value appears on multiple rows?
    Thursday, July 04, 2013 10:44 AM
  • Yes a Contact ID can have many Users. 
    Thursday, July 04, 2013 10:45 AM
  • That *IS* a tricky one.  Ideally there'd be a DISTINCTCOUNTX function :)

    I just wrote the following formula in one of my models:

    =CALCULATE(DISTINCTCOUNT(Users[Contact ID]), FILTER(Users, Users[CompanyID]=CompanyName[CompanyID]))

    I don't know the names of the right columns in your model, but the two columns I use in the FILTER should be the columns that form the relationship between the two tables.

    Let me know on twitter if this works or not, as I don't get notified of new posts as they happen here.

    • Proposed as answer by Rob CollieMVP Thursday, July 04, 2013 11:25 AM
    • Marked as answer by Steve J Wright Thursday, July 04, 2013 11:46 AM
    Thursday, July 04, 2013 11:15 AM