Forum Discussion

loisskinner's avatar
loisskinner
Frequent Visitor
9 years ago
Solved

Attribute a true/false to a Unique Value

Please see the data set below. There is a list of dates that duplicate. I am trying to create a calculated column that will return a true or false value if the date is unique. For instance the first time the date is recorded a 1 will be entered and the second time it appears, a 0. In excel the formulae look like so: =IF(B1094="",0,IF(COUNTIF($B$2:$B1094,B1094)>1,0,1))

I am trying to pull all added columns currently added on excel through to Power BI to eliminate the use of spreadsheets, creating a live feed to Sharepoint.

 

Any help is greatly appreciated, thank you in advance.

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi loisskinner,

    Yes, please add an index column to identity each row in Query Editor by clicking "index column" as follows.



    Then you can create a calculated column using the formula.

    true/false = IF(Table2[Date]<>LOOKUPVALUE(Table2[Date],Table2[Index],Table2[Index]-1),1,0)



    Best Regards,
    Angelia

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi loisskinner,

    I create a date column which are not same with you. Name the tale as Table2, please create a calculated column to using the formula.

    unique = IF( COUNTX(FILTER(Table2,Table2[Date]=EARLIER(Table2[Date])),Table2[Date])>1,"false","Ture")


    You will get the expected result. From the screenshot, only the 2017/6/7 is unique, other dates are duplicate.

     

    Best Regards,
    Angelia

    • loisskinner's avatar
      loisskinner
      Frequent Visitor
      9 years ago

      Thank you for your reply. It has been very useful, but the formulae I am looking for would return a "true" for everytime a unique value is found, please see the attached data. As you can see the value in the left column appears several times but each time a new value presents a true is returned. Is there any way to incorporate this into your formulae?

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi loisskinner,

        Yes, please add an index column to identity each row in Query Editor by clicking "index column" as follows.



        Then you can create a calculated column using the formula.

        true/false = IF(Table2[Date]<>LOOKUPVALUE(Table2[Date],Table2[Index],Table2[Index]-1),1,0)



        Best Regards,
        Angelia