Forum Discussion
Dax Help: Compare duplicate row values
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.
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".
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.
2 Replies
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".
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.