topic Re: Identifying duplicates in DAX Commands and Tips

Identifying duplicates

rmba22875 — Thu, 02 Mar 2023 15:50:07 GMT

We are currently trying to replicate some reporting we already do in excel and use Power BI instead and have hit a snag.

Our data contains 2 identifying markers which we call Home and Away and make up a relationship which look like:

AE001/AE002

AE001/AE004

AE001/AE005

AE001/AE006

AE001/AE008

AE001/AE009

AE001/AE010

AE001/AE012

AE002/AE001

These are always in alphabetical order and we currently use the following formula to identify duplicates.
=IF(ISERROR(INDEX($F$1:F1,MATCH(RIGHT(F2,5)&"/"&LEFT(F2,5),$F$1:F1,0))),"","Duplicate")

Is there something similar that can be done in PowerBI?

Thanks

Re: Identifying duplicates

wdx223_Daniel — Fri, 03 Mar 2023 02:15:44 GMT

it will be easy in M code

NewStep=Table.Group(Table,"Column1",{},1,(x,y)=>let fx=(t)=>Text.Combine(List.Sort(Text.Split(t,"/")),"/") in Value.Compare(fx(x),fx(y)))

in Dax, you can add a calculated column as this

NewColumn=

= VAR _c=test[Column1] RETURN MINX(FILTER(test,test[Column1]=_c||test[Column1]=CONCATENATEX({2,1},PATHITEM(SUBSTITUTE(_c,"/","|"),[Value]),"/")),'test'[Column1])=_c

then you can filter out the false values to get unique value in column 1

Re: Identifying duplicates

FreemanZ — Fri, 03 Mar 2023 02:09:00 GMT

hi rmba22875

try like:

column2 = VAR _list = ALL(TableName[Column1]) RETURN IF( RIGHT([Column1], 5)&"/"&LEFT([Column1], 5) IN _list, "Duplicate" )

it workedl like:

Re: Identifying duplicates

rmba22875 — Fri, 03 Mar 2023 11:26:24 GMT

Ahhh Thank you however what i need is for the second one to only appear as the duplicate otherwise everything would have duplicate in that second column.
Currently my excel formula looks at everything before it and identifies the duplicate from that. Other wise we would get a similar problem with everything being a duplicate.

Re: Identifying duplicates

rmba22875 — Fri, 03 Mar 2023 12:36:02 GMT

Thanks for this but do i need Power Query to do this? or can all of this go into power bi?

Ive tried this so far with no joy:

Duplicate =

= VAR _c='Consolidated Summary'[Consolidated Home Relationship] RETURN MINX(FILTER('Consolidated Summary','Consolidated Summary'[Consolidated Home Relationship]=_c||'Consolidated Summary'[Consolidated Home Relationship]=CONCATENATEX({2,1},PATHITEM(SUBSTITUTE(_c,"/","|"),[Value]),"/")),'Consolidated Summary'[Consolidated Home Relationship])=_c

Re: Identifying duplicates

FreemanZ — Fri, 03 Mar 2023 12:41:15 GMT

hi rmba22875

then you would need an index column, like:

column2 = VAR _list = CALCULATETABLE( VALUES(TableName[Column1]), ALL(TableName), TableName[Index]<EARLIER(TableName[Index]) ) RETURN IF( RIGHT([Column1], 5)&"/"&LEFT([Column1], 5) IN _list, "Duplicate" )

Re: Identifying duplicates

tamerj1 — Fri, 03 Mar 2023 12:45:35 GMT

Hi rmba22875

please try

Duplicate =
VAR T =
VALUES ( 'Table'[Markers] )
VAR HomeMarker =
LEFT ( 'Table'[Markers], 5 )
VAR Away =
RIGHT ( 'Table'[Markers], 5 )
RETURN
IF ( Home > Away && Away & "/" & Home IN T, "Duplicate" )

Re: Identifying duplicates

rmba22875 — Mon, 06 Mar 2023 10:42:25 GMT

My Table has been made from a different query so am currently trying to add an index manually! hopefully once done this will work!

Re: Identifying duplicates

FreemanZ — Mon, 06 Mar 2023 10:57:41 GMT

hi rmba22875

tamej1's solution does not require an index column, please have a try. i tried and succeeded.

Re: Identifying duplicates

rmba22875 — Mon, 06 Mar 2023 11:03:07 GMT

Thanks for this! Sorry I missed it previously.