Dax Help: Compare duplicate row values

[rithu]

I have 4 columns - A,B,C,D in table ASD.
A and B are numbers, C and D are text columns.
A has duplicates.  A and C in all the duplicates rows are equal. (already fixed)

When there are duplicates in A, D in all duplicate rows should also be equal. In this case they are not. 

When D in all the duplicates are not the same I have replace with the value of D from the duplicate row that has the lowest B value  (number). 

For example,  A has duplicates. C is the equal among duplicates. have to replace column D. 

| A | B | C | D |

|---|----|-----|-----|

| 1 | 23 | ASG | OIU | 

| 1 | 25 | ASG | OIG | - 23 is the lowest so final D value should be OIU

| 2 | 30 | XYZ | DEG |

| 2 | 35 | XYZ | DED |  - 30 is the lowest so final D value should be DEG

| 3 | 40 | PQR | LMN | - 36 is the lowest so final D value should be LMF

| 3 | 36 | PQR | LMF | - 

output should be

D

| -----|

OIU |

OIU |

DEG |

DEG |

LMF |

LMF |

How to I write a DAX for this scenario? I am struggling with this, Any kind of help is much appreciated.  Thank you so much. 

[lbendlin · Accepted solution]

Why DAX?  This can be done in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlFwVKhRcAJiZyB2UahRitUBiRoCeUbGQMIx2B1I+nuGAklkOVMkORCpC1KeWaxQkpGqkJNfnlpcolCcr5CWmZeYAzS2LDGnNFWhOCO/NCdFISkVZB7UMCOgXmMDIBERGQVygas73A1gKVMkKaDzFIAWAZUTbRHQPKhpIM+YgCwKCAwCkj6+fmBXG5sRb5iPrxuSYUCdCMPcwMETCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{" A ", Int64.Type}, {" B ", Int64.Type}, {" C ", type text}, {" D ", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Output", (k)=> Table.Sort(Table.SelectRows(#"Changed Type", each k[#" C "]=[#" C "]),{" B ", Order.Ascending}){0}[#" D "])
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[v-jialongy-msft · Accepted solution]

Hi @rithu 

Try the following dax

New D = 
CALCULATE(
    FIRSTNONBLANK('Table'[D], 0),
    FILTER(
        'Table',
        'Table'[A] = EARLIER('Table'[A]) &&
        'Table'[B] = CALCULATE(MIN('Table'[B]), FILTER('Table', 'Table'[A] = EARLIER('Table'[A])))
    )
)

This is the result you want

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

Why DAX?  This can be done in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlFwVKhRcAJiZyB2UahRitUBiRoCeUbGQMIx2B1I+nuGAklkOVMkORCpC1KeWaxQkpGqkJNfnlpcolCcr5CWmZeYAzS2LDGnNFWhOCO/NCdFISkVZB7UMCOgXmMDIBERGQVygas73A1gKVMkKaDzFIAWAZUTbRHQPKhpIM+YgCwKCAwCkj6+fmBXG5sRb5iPrxuSYUCdCMPcwMETCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{" A ", Int64.Type}, {" B ", Int64.Type}, {" C ", type text}, {" D ", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Output", (k)=> Table.Sort(Table.SelectRows(#"Changed Type", each k[#" C "]=[#" C "]),{" B ", Order.Ascending}){0}[#" D "])
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".