DAX Formula for a suggested value - would use index/match if not using DAX

[Vaslo]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Item Number[/TD]
[TD]Identifier[/TD]
[TD]Suggested Item Number[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]9/9 - Seattle[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]9/13 - Fargo[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9/9 - Seattle[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[gazpage]

Generally DAX doesn't guarantee any particular sort order, if it really needs to be the first then you will need add an index column to your data. You could either create this index column, or maybe use a column you have already.

The simple answer would be to just have a measure like:

Suggested:= 
CALCULATE (
    FIRSTNONBLANK( 'Table 0'[Item Number], 1 ),
    ALL ( 'Table 0'[Item Number] )
)

This will return the first item in the column Item Number that is not blank. However, this is based on a sorted list of items, not the order in which they appear in your data.

 

[Ozeroth]

Hi Vaslo,

If you want to do this with a DAX calculated column, something like this should work:

Suggested Item Number =
IF ( 
    NOT ISBLANK ( Data[Item Number] ),
    Data[Item Number],
    CALCULATE ( 
        FIRSTNONBLANK ( Data[Item Number], 0 ),
        ALLEXCEPT ( Data, Data[Identifier] )
    )
)

 

[Vaslo]

Hi Vaslo,

If you want to do this with a DAX calculated column, something like this should work:

Suggested Item Number =
IF ( 
    NOT ISBLANK ( Data[Item Number] ),
    Data[Item Number],
    CALCULATE ( 
        FIRSTNONBLANK ( Data[Item Number], 0 ),
        ALLEXCEPT ( Data, Data[Identifier] )
    )
)

Many thanks to both of you - both seemed to work but I went with the one without the index just to avoid the column. I know sometimes an index is useful so I'll think of some other ways.