FIRSTNONBLANK puzzler

[macfuller]

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.

 

[Ozeroth]

Hi macfuller,

The first argument for FIRSTNONBLANK must be either a reference to a column or a table with a single column.
Since CALCULATE returns a scalar, the first argument in your formula doesn't work.

One quick fix would be to change CALCULATE to CALCULATETABLE:

=
FIRSTNONBLANK (
    CALCULATETABLE (
        VALUES ( NuVia[UNSPSC Code] ),
        FILTER (
            NuVia,
            NuVia[Cust Mfr Part #] = Inventory[CatalogNumber]
                || NuVia[GHX Mfr Part #] = Inventory[CatalogNumber]
        )
    ),
    1
)

Also, another way of writing the formula which should give identical results would be:

=
CALCULATE (
    FIRSTNONBLANK ( NuVia[UNSPSC Code], 1 ),
    FILTER (
        NuVia,
        NuVia[Cust Mfr Part #] = Inventory[CatalogNumber]
            || NuVia[GHX Mfr Part #] = Inventory[CatalogNumber]
    )
)

 

[macfuller]

Thank you so much, that works. I hope that one day I'll be as comfortable with filter, scalar, and vector concepts as the members of this forum!

A quick follow-up question... is it possible (in Excel 2013) to allow the user to choose which column to go against instead of having the OR operator? For example, one can have a dummy measure and use a slicer of measure choices to assign the slicer choice to the dummy. I'd like to do the same for the field so the user could choose either the GHX or our part # into a value and have the code above say ... FILTER(NuVia, NuVia[DummyField] = Inventory[CatalogNumber]...

 

[Matt Allington]

I do t believe it is possible to change the tablename in the code using a slicer. So the only way I could think of is to write each of the formulas inside a switch statement and then return the one you want with the slicer. But be warned that switch is simply an easier way of writing nested if statements, and each option is evaluated each time even though only the selected one is returned, so in some instances it can be slow.