locked
distict values in calculated column RRS feed

  • Question

  • Hi,

    I am new to DAX.

    I have a column in my table that has duplicate values.  I need to create a column in my table that has a 1 for each unique value and a 0 for any duplicate values. I can use distinct count in a measure to return the number of distict values - but I want to use this value in another measure and so need to be able to generate it from the table (calculated column).

    Does anyone know how to do this?

    Thanks.

    Tuesday, March 5, 2013 4:34 AM

Answers

  • I could do this but then had to create a row number column called RowNum. Read through if you can create a row number column if not give a pass.

    Goal - The first value in a column should be 1 and then any rows with the same value in the column should be marked as 0.

    Create a new column call MinRowNumberValue with the following dax statement

    =CALCULATE(MIN(Table1[RowNum]),FILTER(Table1,Table1[Col1]=EARLIER(Table1[Col1])))

    Create a new column called Is Duplicate with the following Dax statement

    =IF(CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Col1]=EARLIER(Table1[Col1])))>1,1,0)

    Now the result you are looking will be like this

    =IF([Is Duplicate]=1,IF([MinRowNumberValue ]=[RowNum],1,0),1)

    Wednesday, March 6, 2013 12:12 AM

All replies

  • I created a table with a single column called column1. The following DAX function will help you to get the desired results.

    =IF(CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Column1]=EARLIER(Table1[Column1])))>1,1,0)

    Please mark the reply as answer if it suits your needs.

    Tuesday, March 5, 2013 10:06 PM
  • Thankyou kindly Srivatsav.

    I tried this and it returned a 0 for any distinct values and a 1 for all the values that had more than a single entry. What I want is something like this.

    1223    1

    1223    0

    1223    0

    1334    1

    1334    0

    1545    1

    Thanks for your help. I really appreciate it.

    Tuesday, March 5, 2013 10:56 PM
  • Is there a surrogate key to this table that I can use? In the sense the surrogate key is an incremental column that seeds by 1 for every insert.
    Tuesday, March 5, 2013 11:10 PM
  • No- there is no surrogate key - 
    Tuesday, March 5, 2013 11:19 PM
  • I could do this but then had to create a row number column called RowNum. Read through if you can create a row number column if not give a pass.

    Goal - The first value in a column should be 1 and then any rows with the same value in the column should be marked as 0.

    Create a new column call MinRowNumberValue with the following dax statement

    =CALCULATE(MIN(Table1[RowNum]),FILTER(Table1,Table1[Col1]=EARLIER(Table1[Col1])))

    Create a new column called Is Duplicate with the following Dax statement

    =IF(CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Col1]=EARLIER(Table1[Col1])))>1,1,0)

    Now the result you are looking will be like this

    =IF([Is Duplicate]=1,IF([MinRowNumberValue ]=[RowNum],1,0),1)

    Wednesday, March 6, 2013 12:12 AM
  • How did you create the "Row Number" column?
    Tuesday, November 19, 2013 5:04 PM