Forum Discussion
Combining two rows with same ID, but selecting only one
Hi,
I'm having some trouble combining rows for products that have the same ID. What I am trying to do is have just one line item for a product that appears more than once in my table.
Current Table Sample
| REF | YEAR | PRODUCT | COST | PERSON |
| A1 | 2023 | Pears | $ 1.50 | Bob |
| B2 | 2023 | Apples | $ 1.30 | Bob |
| B2 | 2023 | Red Apples | $ 1.30 | Jane |
| 2023 | Oranges | $ 1.00 | Jane | |
| C26 | 2024 | Green Lime | $ 1.10 | Bob |
| C26 | 2024 | Lime | $ 1.10 | Lisa |
- not all the items have a REF
- the product name may be a little different, but the REF and COST are really what I need to focus on.
Desired Table Sample
| REF | YEAR | PRODUCT | COST | PERSON |
| A1 | 2023 | Pears | $ 1.50 | Bob |
| B2 | 2023 | Red Apples | $ 1.30 | Jane |
| 2023 | Oranges | $ 1.00 | Jane | |
| C26 | 2024 | Lime | $ 1.10 | Lisa |
I'm not able to essentially "eliminate" a row. Calculated columns don't give me what I need and I've been unsuccessful with measures. This is the latest measure I used, but this just repeats the cost value. Not sure what I'm doing wrong here.
Product_Cost = VAR COST = ADDCOLUMNS('MyTable', "ActualCost", CALCULATE(MAX('MyTable'[Cost]), GROUPBY('MyTable','MyTable'[Ref]))) RETURN MAXX(COST, IF('MyTable'[Cost] = [ActualCost], 'MyTable'[Cost],0))
Any help will be very much appreciated! Thank you!
you can not create a new table, but simply apply a conditional filter to your table using two measures
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhkAP-DuxcAVJ0sSp?e=OHmJ59
3 Replies
- Ahmedx
Super User
you can not create a new table, but simply apply a conditional filter to your table using two measures
Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhkAP-DuxcAVJ0sSp?e=OHmJ59
