locked
How get Distinct Count RRS feed

  • Question

  • Hi All,

    I have excel 2010 and have power pivot installed. I'm trying to get the distinct count of the product code per group but not able to. I have my sample sheet upload here https://app.box.com/s/od5wftgpyjcs5ceej0ho37omqf90xlff

    Can you please have a look and let me know where I'm going wrong?

    Thanks

    Tuesday, March 24, 2015 10:57 AM

Answers

  • Hi Laurence,

    When I try distinctcount I get the error This is not a valid DAX expression. Not sure if distinctcount is available with the powerpivot for excel 2010.

    Regards,

    Jag

    Hi Jaggy99,

    PowerPivot for Excel 2010 in SQL Server 2012 and Excel 2013 support the DISTINCTCOUNT aggregation in DAX. If you use PowerPivot for Excel 2010 in SQL Server 2008 R2. A PivotTable based on PowerPivot data doesn’t offer a native Distinct Count aggregation. To calculate it, you need to create a calculated measure in the PivotTable. Please refer to the link below to see the details.
    http://sqlblog.com/blogs/marco_russo/archive/2009/12/26/distinct-count-measure-in-powerpivot-using-dax.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Friday, April 3, 2015 1:33 AM
    Wednesday, March 25, 2015 9:06 AM

All replies

  • Your formula is using Distinct, which is causing an error per row.  You are then applying a count to each row within the report, hence why you are not getting a distinct count.

    Use Distinct Product Code:=DISTINCTCOUNT([Product Code]) instead which should create the measure (i.e you do not need to apply any calculation within the report layer) and this should work correctly.

    Regards

    Laurence

    • Proposed as answer by greggyb Tuesday, March 24, 2015 9:34 PM
    Tuesday, March 24, 2015 2:17 PM
  • Hi Laurence,

    When I try distinctcount I get the error This is not a valid DAX expression. Not sure if distinctcount is available with the powerpivot for excel 2010.

    Regards,

    Jag

    Tuesday, March 24, 2015 9:58 PM
  • Hi Laurence,

    When I try distinctcount I get the error This is not a valid DAX expression. Not sure if distinctcount is available with the powerpivot for excel 2010.

    Regards,

    Jag

    Hi Jaggy99,

    PowerPivot for Excel 2010 in SQL Server 2012 and Excel 2013 support the DISTINCTCOUNT aggregation in DAX. If you use PowerPivot for Excel 2010 in SQL Server 2008 R2. A PivotTable based on PowerPivot data doesn’t offer a native Distinct Count aggregation. To calculate it, you need to create a calculated measure in the PivotTable. Please refer to the link below to see the details.
    http://sqlblog.com/blogs/marco_russo/archive/2009/12/26/distinct-count-measure-in-powerpivot-using-dax.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Friday, April 3, 2015 1:33 AM
    Wednesday, March 25, 2015 9:06 AM