Marking dupplicates in atable

[Phantomiceman]

Hi Im currently doing an excel to power bi conversion and i need help with working out dax for the followng excel formula which basically marks the first occurence of the 'course reference to' 1 and then any subsequent occurence of the same reference as 0 in a table. This marker is then used later for filtering data.

The current excel formula is

=IF(countif($b$2:b4)>1,0,1) - The B column being the course reference column. Just for reference the current cell formula looks at the previous columns to do its comparrision  . so the  first row formula is

=IF(countif($b$2:b2)>1,0,1)  and then b3's formula is =IF(countif($b$2:b3)>1,0,1) and so fourth..

[JarroVGIT · Accepted solution]

Alright, usually it helps if you give some example data, that would save us time in creating our own dataset to help you out 🙂

Either way, I've created a sample table, in which your question is: how can I mark row 7 and row 9 as second dublicate.

Test table - Row 7 and 9 should be marked 0

The first thing we are going to do in the Query Editor is to add an Index Column. This will help us in determining whether a value has already occurered or if it is the first occurence. Go to Add Column tab, en chose Add Index Column. The table now looks like this:

Our test table, with an Index column added

Now close and apply the query editor en go to the report page. We are going to add a custom column. This column will count the rows when we filter the table on the value column (Column1 in my case) and on the Index column where we want to see only rows where the index is lower or equal as the current row. The formula for the column is this:

Duplicate = IF(CALCULATE(COUNTROWS('Table'), FILTER('Table', 'Table'[Column1] = EARLIER('Table'[Column1]) && 'Table'[Index] <= EARLIER('Table'[Index]))) = 1, 1, 0)

The table now looks like this (from Data view):

Note that row 7 and 9 are marked 0 as the values A en E already occured. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂