Display only first column value in a table with duplicate values

[RuslanP1]

Good day!

I am trying to figure out how to show only the first value of the column. I have a table that contains duplicate values for InvoiceID and InvoiceAmount for each productID. I want to keep the InvoiceID column with all it's values, but the InvoiceAmount should only display the first value, basically I want to get rid of the duplicate value using the measure.

Example:

InvoiceID InvoiceAmount productID
A0246605 999999 item0010
A0246605 999999 Item0079
A0246605 999999 Item0081
A0246605 999999 Item0093
A0246605 999999 Item0166
A0246605 999999 Item0191
A0246605 999999 Item0102
A0246605 999999 Item0470
A0246605 999999 Item0001

The result  I am looking for:

InvoiceID InvoiceAmount productID
A0246605 999999 item0010
A0246605               Item0079
A0246605               Item0081
A0246605             Item0093
A0246605             Item0166
A0246605             Item0191
A0246605             Item0102
A0246605             Item0470
A0246605             Item0001

Thanks in advance!

[v-lili6-msft · Accepted solution]

hi  @RuslanP1 

If you want to create a measure, just try this measure

Measrue = 
var _firstrow=CALCULATE(MIN('Table'[productID]),ALLEXCEPT('Table','Table'[InvoiceID],'Table'[InvoiceAmount])) 
return
CALCULATE(MIN('Table'[InvoiceAmount]),FILTER('Table','Table'[productID]=_firstrow))

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[swise001]

@RuslanP1 

Here's a solution using Power Query and your sample data table: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMjEzMzBVsAQDhcyS1FwDA0MDpVgdTElPsKS5JR5JC0M8kpbGuCUNzczwSFriMdbQwAi3pIk5Pq8YAI2NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceID InvoiceAmount productID" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "InvoiceID InvoiceAmount productID", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"InvoiceID InvoiceAmount productID.1", "InvoiceID InvoiceAmount productID.2", "InvoiceID InvoiceAmount productID.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"InvoiceID InvoiceAmount productID.1", "InvoiceID"}, {"InvoiceID InvoiceAmount productID.2", "InvoiceAmount"}, {"InvoiceID InvoiceAmount productID.3", "productID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"InvoiceAmount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"InvoiceID"}, {{"All", each _, type table [InvoiceID=nullable text, InvoiceAmount=nullable number, productID=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WithIndex", each Table.AddIndexColumn([All],"Index",1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"WithIndex"}),
    #"Expanded WithIndex" = Table.ExpandTableColumn(#"Removed Other Columns", "WithIndex", {"InvoiceID", "InvoiceAmount", "productID", "Index"}, {"InvoiceID", "InvoiceAmount", "productID", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded WithIndex", "Invoice Amount", each if [Index] = 1 then [InvoiceAmount] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"InvoiceAmount", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"InvoiceID", "Invoice Amount", "productID"})
in
    #"Reordered Columns"

Just Copy Paste this into the Advanced Editor for a new query.  A similar pattern could be applied to your table.   

[amitchandak]

@RuslanP1 , See if these measures can help

sumx(values(Table[InvoiceID]), min(Table[InvoiceAmount]))

sumx(values(Table[InvoiceID]),Firstnonblankvalue(productID, min(Table[InvoiceAmount])))

[RuslanP1]

Thank you for the quick reply @amitchandak

I checked the suggested measures and both of them returned the same InvoiceAmount for each row:

InvoiceID	productID	Measure	Measure 3	InvoiceAmount
A0246605	item0010	999999	999999	999999
A0246605	Item0079	999999	999999	999999
A0246605	Item0081	999999	999999	999999
A0246605	Item0093	999999	999999	999999
A0246605	Item0166	999999	999999	999999
A0246605	Item0191	999999	999999	999999
A0246605	Item0102	999999	999999	999999
A0246605	Item0470	999999	999999	999999
A0246605	Item0001	999999	999999	999999

Measure "Measure" - sumx(values(Table[InvoiceID]), min(Table[InvoiceAmount]))

Measure "Measure 3" - sumx(values(Table[InvoiceID]),Firstnonblankvalue(productID, min(Table[InvoiceAmount])))

[Greg_Deckler]

@RuslanP1 Is there something to identify "First" like a date column or Index? Index would be good then it would be easy. Just find the MINX at that point. Basically a Lookup Min/Max. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434