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

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , See if these measures can help

 

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

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

  • 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])))