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

Vaslo

Board Regular
Joined
Jun 3, 2009
Messages
159
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

ABC
Item NumberIdentifierSuggested Item Number
259/9 - Seattle25
319/13 - Fargo31
9/9 - Seattle25

<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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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:

Code:
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.
 
Upvote 0
Hi Vaslo,

If you want to do this with a DAX calculated column, something like this should work:
Code:
Suggested Item Number =
IF ( 
    NOT ISBLANK ( Data[Item Number] ),
    Data[Item Number],
    CALCULATE ( 
        FIRSTNONBLANK ( Data[Item Number], 0 ),
        ALLEXCEPT ( Data, Data[Identifier] )
    )
)
 
Upvote 0
Hi Vaslo,

If you want to do this with a DAX calculated column, something like this should work:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top