topic Re: How to create a check column to identify duplicated composite values in a single data set in DAX Commands and Tips

How to create a check column to identify duplicated composite values in a single data set

IDAronson — Mon, 14 Nov 2022 23:30:31 GMT

Hello.

I have been tossed a project that I feel a little in over my head on. I have been trying to puzzle this out today, I feel like I am starting to understand some of what I need to do. But part of it escapes me. Simply comparing has been easy to figure out. But having to look at this as a composite value is throwing me. Any tips, help or otherwise is very much appreciated. I have a set of data that has a series of ID numbers and a corresponding Text field.

What I am attempting to do, is create a check column that will help me identify duplicated combination values. So for each Pair, I want to know if there is another identical pair within the data set.

So for each row. It needs to look at the Number value and the Text. Then for each time the same number value is present. It needs to check if that first text was also present. If it is, I would like it to indicate that with a True False. Something like that. Lets say this first table is an example. The combination of Number = 1 and Text = Text 1 occurs twice. In my first row and my 5th row. So next to each of those rows, in my new column I would want it to display True. Any others that don't match it can say false.

Number	Text
1	Text 1
2	Text 2
3	Text 3
4	Text 4
1	Text 1
2	Text 1
3	Text 3
4	Text 2
1	Text 3

Ideally looking something like this. Row 1 matches Row 5, so they are true. Row 3 matches with row 7, so they are true as well.

Number	Text	Duplicate
1	Text 1	True
2	Text 2	False
3	Text 3	True
4	Text 4	False
1	Text 1	True
2	Text 1	False
3	Text 3	True
4	Text 2	False
1	Text 3	False

Re: How to create a check column to identify duplicated composite values in a single data set

FreemanZ — Mon, 14 Nov 2022 23:45:45 GMT

Supposing your table named Data, try to add a column with the code below:

Duplicate =

VAR CurrentText=Data[Text]

VAR CurrentNumber=Data[Number]

VAR _count = COUNTROWS(

FILTER(

Data,

Data[Number]=CurrentNumber,

Data[Text]=CurrentText

)

RETURN

IF(_count>=2, true, false)

Re: How to create a check column to identify duplicated composite values in a single data set

v-jialluo-msft — Tue, 15 Nov 2022 07:13:07 GMT

^{Hi IDAronson ,}

^{FreemanZ was basically right, but there were some minor mistakes}

Duplicate = VAR CurrentText='Table'[text] VAR CurrentNumber='Table'[number] VAR _count = COUNTROWS( FILTER( 'Table', 'Table'[number]=CurrentNumber && 'Table'[text]=CurrentText ) ) RETURN IF(_count>=2, true, false)

^{Best Regards,}

^{Gallen Luo}

Re: How to create a check column to identify duplicated composite values in a single data set

FreemanZ — Tue, 15 Nov 2022 07:42:21 GMT

Aha, thanks for pointing out. FILTER can't take logic test argument like that.

It needs to be:

'Table'[number]=CurrentNumber && 'Table'[text]=CurrentText

AND(Data[Number]=CurrentNumber, Data[Text]=CurrentText))

Listing multiple logic test arguments is more common with CALCULATE.

Re: How to create a check column to identify duplicated composite values in a single data set

IDAronson — Tue, 15 Nov 2022 15:25:47 GMT

Thanks for the help. This got me close. Then with Gallen's help that took me the rest of the way. Appreciate your help.

Re: How to create a check column to identify duplicated composite values in a single data set

IDAronson — Tue, 15 Nov 2022 15:26:30 GMT

Thank you so much. Appreciate the help. Now I need to make sure I fully understand how this works so that I can do it myself next time.