I am trying to recreate much of a clunky spreadsheet that suggests item numbers for products that didn't have item numbers. One calc performed in Excel is an Index/Match within same table. I ultimately have some choices for a number in earlier columns, but when I run out of choices, I try to look at items sold at the same time and same city to give me a "suggested" number. So in short I will have something like this:
-An identifier that has the date sold + the city it was sold to
-A column number of item numbers
<tbody>
</tbody>
In the simple example, it suggests 25 because it matches on the first instance of the identifier that matches it, suggesting 25 as the possible number. I do this (formula in col C) with an index/match now:
=INDEX([Item Number],MATCH([@Identifier],[Identifier],0))
It's clunky because of the size of the data, and it's just kind of a weird formula. All the blank data makes it hard to index other lookup tables, which is also why I am trying to get every value at least a suggested number to work with. Is there a way this can be done in DAX so I can keep all this stuff in DAX before resorting to Excel formulas?
Thanks in advance.
-An identifier that has the date sold + the city it was sold to
-A column number of item numbers
A | B | C |
Item Number | Identifier | Suggested Item Number |
25 | 9/9 - Seattle | 25 |
31 | 9/13 - Fargo | 31 |
9/9 - Seattle | 25 |
<tbody>
</tbody>
In the simple example, it suggests 25 because it matches on the first instance of the identifier that matches it, suggesting 25 as the possible number. I do this (formula in col C) with an index/match now:
=INDEX([Item Number],MATCH([@Identifier],[Identifier],0))
It's clunky because of the size of the data, and it's just kind of a weird formula. All the blank data makes it hard to index other lookup tables, which is also why I am trying to get every value at least a suggested number to work with. Is there a way this can be done in DAX so I can keep all this stuff in DAX before resorting to Excel formulas?
Thanks in advance.