Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Display only first column value in a table with duplicate values

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 kee...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    hi  Anonymous 

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you for the quick reply amitchandak

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

 

InvoiceIDproductIDMeasureMeasure 3InvoiceAmount
A0246605item0010999999 999999 999999 
A0246605Item0079999999 999999 999999 
A0246605Item0081999999 999999 999999 
A0246605Item0093999999 999999 999999 
A0246605Item0166999999 999999 999999 
A0246605Item0191999999 999999 999999 
A0246605Item0102999999 999999 999999 
A0246605Item0470999999 999999 999999 
A0246605Item0001999999 999999 999999 

 

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

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