PowerPivot: How can I count distinct values in a calculated column where 2 or more rows match. Dax Function RRS feed

  • Question

  • In the data model below I have combined 4 columns into 1 column (column1&" - "&column2&" - "&column3&" - "&column4). I need to somehow count the number of times 2 or more rows match in the new calculated column. (I can't figure out how to verify my email to upload an example)

    So if 2 rows match that would be a 1. If 4 rows match thats a 1. If 1 row is distinct that would be a 0. A blank row would also be a 0

    There must be a way to add something along with DISTINCTCOUNT to do this. I just cant figure it out and bing/google hasn't been much help. 


    Column1: 2/5/2017

    Column2: 500

    Column3: Phoenix, AZ

    Column4: 6B9M

    Calculated Column: 2/5/2017 - 500 - Phoenix, AZ - 6B9M

    I have thousands of rows of data and need to calculate when I have 2 or more of the calculated column rows match. I will then drop this into an existing pivot table. 

    Thursday, April 13, 2017 4:35 PM


All replies

  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    Easier with PQ.
    Faster, less memory.
    PQ available in all versions of Excel.
    PP only available in Pro Plus version.
    Anything that can be loaded into PP can be loaded into PQ.
    PQ can be loaded into the PP Data Model.

    Thursday, April 13, 2017 11:33 PM
  • Hi rizz0555,

    I can see that your issue is related with Powerpivot forum.

    this forum only handles Excel object model related issues.

    so for better response and better solution , I move this thread to Powerpivot forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thank you for your understanding.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 14, 2017 3:29 AM
  • Please kindly mark the correct solutions as answers, this could be beneficial to other community members who are facing similar issue. Your contribution is highly appreciated.


    Thursday, January 18, 2018 11:45 AM