Duplicate Rows Based On Cell Value In different Column

[Technowolf]

Details about my table

One Serial no can contain  multiple Case ID's

Each case is marked duplicate or Non Duplicate.

I have a column labled "SN With Non-Duplicates" whick tells how many non duplicate cases are there for that Serial No.

Based on that value  I want to duplicate the rows with has the value duplicate in the column "Duplicate check". 

if column "duplicate check"  value is non-duplicate its should not duplicate the row.

if column labled "SN With Non-Duplicates" has value 1 or 0 its . then the rows with the value duplicate should not be duplicated.

Raw data table

Raw table

Result table. this is how the end result sould be.

Result Table

Please help me with this logic in Dax.

here is a sample some one has done in excel

https://www.extendoffice.com/documents/excel/4054-excel-duplicate-rows-based-on-cell-value.html

Regards,

Charles 

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

We can meet your requirement easier in the Power Query Editor than Dax way:

1. Create a custom column use following formula:

let 
    SN = [#"SN With Non-Duplicates"] 
in 
    if [Duplicate Check] = "Duplicate" 
        and SN <> 0 
        and SN <> 1 
    then List.Repeat({SN},SN)
    else {SN}

2. After get the column contain list, we expand it to the new rows

3. Delete the old SN columns then rename the custom as the origin name.

4. Then we can get the result table

We suggest you to delete the the screenshot you have shared if it contain any confidential information or it come from real data. If you still want the DAX solution, just tell us and we will try to make it.

All the M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS/TsQwDIffpXOR4iRN2xEo3HYvUHVApxuQENwA70/SxK6dPxyDvbRf7Xz5qevaPXZ9pydsoH1zqcDX+evzYfm5fbxf3r6v+ILutr4gjW9jKlC+cWyU1EyUTXMAv82puUKFBsP+IBbAf6lwJpPKZdDUgsLmNtV+RmGjRYXPDakKGbZFzUy+zUe1ZmnFxOcGjYCMIoicV6C5hCKJynVtP9WiULlJ4+7sFyFUbjEkRRCPeU8SRe8YkvzC1A49Swi1uyQy9wE1CK2PmMl8SUvkEiAgcuKxL1J1WDkwoKvD3NekQIOMS5PwP3W+SBL4TeRRduTzVQDA7mDKmGFfo2T4r2dsHa4Gij9PLStOgpofi29bTCTuRJxmycKNOaOjke0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Case ld" = _t, #"Case Created Date" = _t, #"Case Closed Date" = _t, #"Product Series Name" = _t, #"# of Cases" = _t, #"Index Ranking" = _t, #"Duplicate Check" = _t, #"# Cases Per SN" = _t, #"SN With Non-Duplicates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Case ld", Int64.Type}, {"Case Created Date", Int64.Type}, {"Case Closed Date", Int64.Type}, {"Product Series Name", Int64.Type}, {"# of Cases", Int64.Type}, {"Index Ranking", Int64.Type}, {"Duplicate Check", type text}, {"# Cases Per SN", Int64.Type}, {"SN With Non-Duplicates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    SN = [#"SN With Non-Duplicates"] 
in 
    if [Duplicate Check] = "Duplicate" 
        and SN <> 0 
        and SN <> 1 
    then List.Repeat({SN},SN)
    else {SN}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"SN With Non-Duplicates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "SN With Non-Duplicates"}})
in
    #"Renamed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

We can create the list contain the case id that they need to compare, just need to use the columns of [Serial Number] , [Duplicate Check] and the [Case ld]

let 
    SerialNumber = [Serial Number],
    SN_Table = Table.SelectRows(
        #"Changed Type",
        each [Serial Number] = SerialNumber 
            and [Duplicate Check]="Non-Duplicate"
        ),
    n = Table.RowCount(SN_Table),
    SerialNumberListOfNonD = 
        Table.ToList(
            Table.TransformColumnTypes(
                Table.SelectColumns(SN_Table,"Case ld"),
                {{"Case ld", type text}}
            )
        )
in 
    if [Duplicate Check] = "Duplicate" 
        and n > 1
    then SerialNumberListOfNonD
    else {"No-Case-To-Compare"}

then expand the list as previous reply.

All the M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS/TsQwDIffpXOR4iRN2xEo3HYvUHVApxuQENwA70/SxK6dPxyDvbRf7Xz5qevaPXZ9pydsoH1zqcDX+evzYfm5fbxf3r6v+ILutr4gjW9jKlC+cWyU1EyUTXMAv82puUKFBsP+IBbAf6lwJpPKZdDUgsLmNtV+RmGjRYXPDakKGbZFzUy+zUe1ZmnFxOcGjYCMIoicV6C5hCKJynVtP9WiULlJ4+7sFyFUbjEkRRCPeU8SRe8YkvzC1A49Swi1uyQy9wE1CK2PmMl8SUvkEiAgcuKxL1J1WDkwoKvD3NekQIOMS5PwP3W+SBL4TeRRduTzVQDA7mDKmGFfo2T4r2dsHa4Gij9PLStOgpofi29bTCTuRJxmycKNOaOjke0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Case ld" = _t, #"Case Created Date" = _t, #"Case Closed Date" = _t, #"Product Series Name" = _t, #"# of Cases" = _t, #"Index Ranking" = _t, #"Duplicate Check" = _t, #"# Cases Per SN" = _t, #"SN With Non-Duplicates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Case ld", Int64.Type}, {"Case Created Date", Int64.Type}, {"Case Closed Date", Int64.Type}, {"Product Series Name", Int64.Type}, {"# of Cases", Int64.Type}, {"Index Ranking", Int64.Type}, {"Duplicate Check", type text}, {"# Cases Per SN", Int64.Type}, {"SN With Non-Duplicates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    SerialNumber = [Serial Number],
    SN_Table = Table.SelectRows(
        #"Changed Type",
        each [Serial Number] = SerialNumber 
            and [Duplicate Check]="Non-Duplicate"
        ),
    n = Table.RowCount(SN_Table),
    SerialNumberListOfNonD = 
        Table.ToList(
            Table.TransformColumnTypes(
                Table.SelectColumns(SN_Table,"Case ld"),
                {{"Case ld", type text}}
            )
        )
in 
    if [Duplicate Check] = "Duplicate" 
        and n > 1
    then SerialNumberListOfNonD
    else {"No-Case-To-Compare"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Please let us know if the [Duplicate Check] column is also created from DAX formula.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

After creating the "Case-To-Compare" column in the power query editor, we can create a calculate colmun use dax to comprae the valid status:

Condition 1 = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF ( [Case Created Date] <= compareClosed, "TRUE", "FALSE" )
)

Condition 2 = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    RETURN
        IF ( [Case Closed Date] >= compareCreated, "TRUE", "FALSE" )
)

Pre_Valid_Status_2 =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    IF (
        AND ( [Condition 1] = "TRUE", [Condition 2] = "TRUE" ),
        "Valid Duplicate Created",
        "Invalid Duplicate Created"
    )
)

Or Merge them into one

Pre_Valid_Status = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF (
            AND (
                [Case Created Date] <= compareClosed,
                [Case Closed Date] >= compareCreated
            ),
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

If you want to the logical that in same duplicate case, there are at least one valid and end restult is valid, we can create a calculated column use following formula:

End_Valid_Status = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR SerialNumber = [Serial Number]
    VAR CaseId = [Case Id]
    VAR t =
        FILTER ( 'Table', [Serial Number] = SerialNumber && [Case Id] = CaseId )
    RETURN
        IF (
            COUNTROWS ( FILTER ( t, [Pre_Valid_Status] = "Valid Duplicate Created" ) ) > 0,
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

We can meet your requirement easier in the Power Query Editor than Dax way:

1. Create a custom column use following formula:

let 
    SN = [#"SN With Non-Duplicates"] 
in 
    if [Duplicate Check] = "Duplicate" 
        and SN <> 0 
        and SN <> 1 
    then List.Repeat({SN},SN)
    else {SN}

2. After get the column contain list, we expand it to the new rows

3. Delete the old SN columns then rename the custom as the origin name.

4. Then we can get the result table

We suggest you to delete the the screenshot you have shared if it contain any confidential information or it come from real data. If you still want the DAX solution, just tell us and we will try to make it.

All the M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS/TsQwDIffpXOR4iRN2xEo3HYvUHVApxuQENwA70/SxK6dPxyDvbRf7Xz5qevaPXZ9pydsoH1zqcDX+evzYfm5fbxf3r6v+ILutr4gjW9jKlC+cWyU1EyUTXMAv82puUKFBsP+IBbAf6lwJpPKZdDUgsLmNtV+RmGjRYXPDakKGbZFzUy+zUe1ZmnFxOcGjYCMIoicV6C5hCKJynVtP9WiULlJ4+7sFyFUbjEkRRCPeU8SRe8YkvzC1A49Swi1uyQy9wE1CK2PmMl8SUvkEiAgcuKxL1J1WDkwoKvD3NekQIOMS5PwP3W+SBL4TeRRduTzVQDA7mDKmGFfo2T4r2dsHa4Gij9PLStOgpofi29bTCTuRJxmycKNOaOjke0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Case ld" = _t, #"Case Created Date" = _t, #"Case Closed Date" = _t, #"Product Series Name" = _t, #"# of Cases" = _t, #"Index Ranking" = _t, #"Duplicate Check" = _t, #"# Cases Per SN" = _t, #"SN With Non-Duplicates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Case ld", Int64.Type}, {"Case Created Date", Int64.Type}, {"Case Closed Date", Int64.Type}, {"Product Series Name", Int64.Type}, {"# of Cases", Int64.Type}, {"Index Ranking", Int64.Type}, {"Duplicate Check", type text}, {"# Cases Per SN", Int64.Type}, {"SN With Non-Duplicates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    SN = [#"SN With Non-Duplicates"] 
in 
    if [Duplicate Check] = "Duplicate" 
        and SN <> 0 
        and SN <> 1 
    then List.Repeat({SN},SN)
    else {SN}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"SN With Non-Duplicates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "SN With Non-Duplicates"}})
in
    #"Renamed Columns"

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jaisveer21]

@Technowolf , @v-lid-msft  Can anyone help me to create those 3 columns as well??- 1)Duplicate check 2) index ranking 3) SN with non-duplicate

Please its urgent

Thanks in advance!!

[jaisveer21]

@Technowolf How did you create duplicate check,indexranking and snwith duplicate column?

[Technowolf]

Hi 

Thanks for the awesome solution. 

The info available in the snapshot are dummy

I have another issue the columns I use for this calcualtions are DAX caluclated columns in power query this columns are not visble .

SN With Duplicates =

IF (

CALCULATE (

SUM ( 'Case'[Dup Cal] ),

FILTER ( 'Case', 'Case'[Index Ranking] = EARLIER ( 'Case'[Index Ranking] ) )

) > 0,

"Yes",

"No"

)

and 

Index Ranking = RANKX('Case','Case'[Serial Number],,ASC,Dense)

I am ranking them so that i group my serial numbers with same rank no.

I will tell you why I am Creating duplicates. May be you can let me know what is the best way I can do this.

I need to compare the Duplicte cases with non duplicate Case.

If i have one Dupicate case and two non duplicate case. then this  duplicate case should check it self with both non duplicate case and see if it valid based on the condition

Conditions:

1. if created date of a duplicate case is less than Closed date of a Non Duplicate case = true

2. if closed date of a duplicate case is greater than created date of the non-Duplicate case = true

if both the conditions are true then its a valid duplicate case created for the same Serial no.

The Complexicity is when I have one Duplicate and more than one non-duplicate case for the same serial no. How do I make one duplicate case  check it self twice  and capture the value Valid if it satififies those condition for any one of the Non duplicate cases. .

Hope I am able to explain this properly.

Please let me know if you need any more clarifications 

Regards,

Charles 

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

We can create the list contain the case id that they need to compare, just need to use the columns of [Serial Number] , [Duplicate Check] and the [Case ld]

let 
    SerialNumber = [Serial Number],
    SN_Table = Table.SelectRows(
        #"Changed Type",
        each [Serial Number] = SerialNumber 
            and [Duplicate Check]="Non-Duplicate"
        ),
    n = Table.RowCount(SN_Table),
    SerialNumberListOfNonD = 
        Table.ToList(
            Table.TransformColumnTypes(
                Table.SelectColumns(SN_Table,"Case ld"),
                {{"Case ld", type text}}
            )
        )
in 
    if [Duplicate Check] = "Duplicate" 
        and n > 1
    then SerialNumberListOfNonD
    else {"No-Case-To-Compare"}

then expand the list as previous reply.

All the M Query is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS/TsQwDIffpXOR4iRN2xEo3HYvUHVApxuQENwA70/SxK6dPxyDvbRf7Xz5qevaPXZ9pydsoH1zqcDX+evzYfm5fbxf3r6v+ILutr4gjW9jKlC+cWyU1EyUTXMAv82puUKFBsP+IBbAf6lwJpPKZdDUgsLmNtV+RmGjRYXPDakKGbZFzUy+zUe1ZmnFxOcGjYCMIoicV6C5hCKJynVtP9WiULlJ4+7sFyFUbjEkRRCPeU8SRe8YkvzC1A49Swi1uyQy9wE1CK2PmMl8SUvkEiAgcuKxL1J1WDkwoKvD3NekQIOMS5PwP3W+SBL4TeRRduTzVQDA7mDKmGFfo2T4r2dsHa4Gij9PLStOgpofi29bTCTuRJxmycKNOaOjke0X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Serial Number" = _t, #"Case ld" = _t, #"Case Created Date" = _t, #"Case Closed Date" = _t, #"Product Series Name" = _t, #"# of Cases" = _t, #"Index Ranking" = _t, #"Duplicate Check" = _t, #"# Cases Per SN" = _t, #"SN With Non-Duplicates" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial Number", type text}, {"Case ld", Int64.Type}, {"Case Created Date", Int64.Type}, {"Case Closed Date", Int64.Type}, {"Product Series Name", Int64.Type}, {"# of Cases", Int64.Type}, {"Index Ranking", Int64.Type}, {"Duplicate Check", type text}, {"# Cases Per SN", Int64.Type}, {"SN With Non-Duplicates", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
    SerialNumber = [Serial Number],
    SN_Table = Table.SelectRows(
        #"Changed Type",
        each [Serial Number] = SerialNumber 
            and [Duplicate Check]="Non-Duplicate"
        ),
    n = Table.RowCount(SN_Table),
    SerialNumberListOfNonD = 
        Table.ToList(
            Table.TransformColumnTypes(
                Table.SelectColumns(SN_Table,"Case ld"),
                {{"Case ld", type text}}
            )
        )
in 
    if [Duplicate Check] = "Duplicate" 
        and n > 1
    then SerialNumberListOfNonD
    else {"No-Case-To-Compare"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Please let us know if the [Duplicate Check] column is also created from DAX formula.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Technowolf]

Hi

Your solution worked like a charm, its amazing. I was breaking my head over this for over a month, every on said it was not possible in PowerBi, People were suggesting Python. I am so happy and greatfull for your help. I need further more help in this report I am creating.

I have Some Conditions which I have mentioned earlier replies. Can you me with too.

1. if created date of a duplicate case is less than Closed date of a Non Duplicate case = true

2. if closed date of a duplicate case is greater than created date of the non-Duplicate case = true

Snapshot of the end Result.

If you see I am comparing the duplicate case Created and Closed dates with the non duplicate case Created and closed dates. This beacuse A duplicate case cant be created after a non-duplicate case was closed and Duplicate case cant be closed before the Non Duplicate case was created.

This is what my Condition 1 and Condition 2 is doing.

in this senario you see the first dulicate case failed one condition and passed in the second Case. so therefore its was created for the second Non duplicate case.  so this is a valid duplicase case  so my end result should be valid Duplicate

Please let me know if you need any more clarifications. and you help will be very much appriciated.

Regards,

Charles Thangaraj

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

After creating the "Case-To-Compare" column in the power query editor, we can create a calculate colmun use dax to comprae the valid status:

Condition 1 = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF ( [Case Created Date] <= compareClosed, "TRUE", "FALSE" )
)

Condition 2 = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    RETURN
        IF ( [Case Closed Date] >= compareCreated, "TRUE", "FALSE" )
)

Pre_Valid_Status_2 =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    IF (
        AND ( [Condition 1] = "TRUE", [Condition 2] = "TRUE" ),
        "Valid Duplicate Created",
        "Invalid Duplicate Created"
    )
)

Or Merge them into one

Pre_Valid_Status = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF (
            AND (
                [Case Created Date] <= compareClosed,
                [Case Closed Date] >= compareCreated
            ),
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

If you want to the logical that in same duplicate case, there are at least one valid and end restult is valid, we can create a calculated column use following formula:

End_Valid_Status = 
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    BLANK (),
    VAR SerialNumber = [Serial Number]
    VAR CaseId = [Case Id]
    VAR t =
        FILTER ( 'Table', [Serial Number] = SerialNumber && [Case Id] = CaseId )
    RETURN
        IF (
            COUNTROWS ( FILTER ( t, [Pre_Valid_Status] = "Valid Duplicate Created" ) ) > 0,
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Technowolf]

Hi

Need one more little tweek to my report there are some case where the Duplicate check is Duplicate and Case _To_Compare is No-Case_To_Compare. These Cases should also be considered has Invalid Duplicate Created, both in Pre_valid_Status and END_Valid Status.

Can you help me with this too?

Regards,

Charles Thangaraj

[v-lid-msft · Accepted solution]

Hi @Technowolf ,

We can try to change the DAX formula to following: 

Pre_Valid_Status =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    IF ( [Duplicate Check] = "Duplicate", "Invalid Duplicate Created", BLANK () ),
    VAR comareid =
        VALUE ( [Case_To_Compare] )
    VAR compareCreated =
        LOOKUPVALUE ( 'Table'[Case Created Date], 'Table'[Case Id], comareid )
    VAR compareClosed =
        LOOKUPVALUE ( 'Table'[Case Closed Date], 'Table'[Case Id], comareid )
    RETURN
        IF (
            AND (
                [Case Created Date] <= compareClosed,
                [Case Closed Date] >= compareCreated
            ),
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

End_Valid_Status =
IF (
    [Case_To_Compare] = "No-Case-To-Compare",
    IF ( [Duplicate Check] = "Duplicate", "Invalid Duplicate Created", BLANK () ),
    VAR SerialNumber = [Serial Number]
    VAR CaseId = [Case Id]
    VAR t =
        FILTER ( 'Table', [Serial Number] = SerialNumber && [Case Id] = CaseId )
    RETURN
        IF (
            COUNTROWS ( FILTER ( t, [Pre_Valid_Status] = "Valid Duplicate Created" ) ) > 0,
            "Valid Duplicate Created",
            "Invalid Duplicate Created"
        )
)

Best regards,

[Technowolf]

Hi

It worked like a charm again, Thanks a ton.

Regards,

Charles Thangaraj

[Technowolf]

Hi

This worked liked a charm, Thanks a Million for all you help. I will ask if need any further help. you have made my impossible report possible. wen everyone said with Dax or PowerQuery is not possilble. You will have to do using Python or SQL outside PowerBi.

I really appriciate your time and effort.

Regards,

Charles Thangaraj