I'm unable to return what I think should be a simple lookup and could use some help.
We have an inventory table [Inventory] and a table with UNSPSC codes [NuVia]. I can't relate the tables in the data model because the UNSPSC table has multiple values in the key field that we need for other purposes.
The items are linked by the manufacturer catalog number. However, our catalog number and the provider of the UNSPSC table's catalog number may not match, so I need to check on both. They at least are both columns in the UNSPSC table. So the logic is that I'd like to return the first value of UNSPSC code from the UNSPSC table that matches either our catalog number or the provider's catalog number.
I'm doing this as a calculated column in the Inventory table. I can get this code to return how many matches there are:
=CALCULATE(COUNTROWS(NuVia), FILTER(NuVia, NuVia[Cust Mfr Part #] = Inventory[CatalogNumber] || NuVia[GHX Mfr Part #] = Inventory[CatalogNumber]))
But all my attempts to do a FIRSTNONBLANK with VALUES(NuVia[UNSPSC Code]) are throwing error messages. For example, this formula throws a "The first argument to FIRSTNONBLANK must specify a column."
=FIRSTNONBLANK(CALCULATE(VALUES(NuVia[UNSPSC Code]), FILTER(NuVia, NuVia[Cust Mfr Part #] = Inventory[CatalogNumber] || NuVia[GHX Mfr Part #] = Inventory[CatalogNumber])),1)
I've tried other combinations but I'm just not getting it. I appreciate your help.
We have an inventory table [Inventory] and a table with UNSPSC codes [NuVia]. I can't relate the tables in the data model because the UNSPSC table has multiple values in the key field that we need for other purposes.
The items are linked by the manufacturer catalog number. However, our catalog number and the provider of the UNSPSC table's catalog number may not match, so I need to check on both. They at least are both columns in the UNSPSC table. So the logic is that I'd like to return the first value of UNSPSC code from the UNSPSC table that matches either our catalog number or the provider's catalog number.
I'm doing this as a calculated column in the Inventory table. I can get this code to return how many matches there are:
=CALCULATE(COUNTROWS(NuVia), FILTER(NuVia, NuVia[Cust Mfr Part #] = Inventory[CatalogNumber] || NuVia[GHX Mfr Part #] = Inventory[CatalogNumber]))
But all my attempts to do a FIRSTNONBLANK with VALUES(NuVia[UNSPSC Code]) are throwing error messages. For example, this formula throws a "The first argument to FIRSTNONBLANK must specify a column."
=FIRSTNONBLANK(CALCULATE(VALUES(NuVia[UNSPSC Code]), FILTER(NuVia, NuVia[Cust Mfr Part #] = Inventory[CatalogNumber] || NuVia[GHX Mfr Part #] = Inventory[CatalogNumber])),1)
I've tried other combinations but I'm just not getting it. I appreciate your help.