Remove Duplicates using DAX

[SushmaReddy]

Hi Team,

I am developing a report where i need to use table visual and have to display the columns as shown below.

Col1     Col2     Col3

Test1    .abc       .123

            .abc       .234

            .abc       .345

Where i am using a measure to get Col2 and Col3 

IF(

         HASONEVALUE(Table1[Col1]),

         CONCATENATEX(Table2,". "&

         Table2[Col2],

         UNICHAR(10)&UNICHAR(13),

         Table2[Col3])

)

but the expected output is shown below.

Col1     Col2     Col3

Test1    .abc       .123

                         .234

                         .345

[Mahesh0016]

@SushmaReddy I hope this helps you!THANK YOU!!
BKC =
IF (
ISINSCOPE ( Table1[Col1] ),
CONCATENATEX (
VALUES ( Table2 ),
". " & Table2[Col2],
UNICHAR ( 10 ) & UNICHAR ( 13 ),
Table2[Col3]
)
)

[SushmaReddy]

Hi Thank you for the immediate response but i am still getting the same output.

[danextian]

Hi @SushmaReddy ,

Assuming that you want to keep only the first Col3 row the min data by Col1 and Col2, create a calc column using the formula below (change the table and column names accordingly):

=
CALCULATE ( MIN ( [Col3] ), ALLEXCEPT ( Table1, Table1[Col1], Table1[Col2] ) ) = Table1[Col3]

This will return 1/0. Use this column in the filter pane. Set the value to 1.

Or you can use this in a measure

=
CALCULATE ( [Original Measure], FILTER ( Table1, Table1[MinValue] = 1 ) )