Attribute a true/false to a Unique Value

[loisskinner]

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.

[v-huizhn-msft]

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]

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 · Accepted solution]

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

[loisskinner]

That worked a treat! thank you so much