topic Re: Dax Help: Compare duplicate row values in DAX Commands and Tips

Dax Help: Compare duplicate row values

rithu — Mon, 29 Jan 2024 23:33:57 GMT

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

| -----|

OIU |

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.

Re: Dax Help: Compare duplicate row values

lbendlin — Tue, 30 Jan 2024 02:12:28 GMT

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".

Re: Dax Help: Compare duplicate row values

Anonymous — Tue, 30 Jan 2024 02:36:29 GMT

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.