find the first occurrence of a value based on a selection

Question

I am using the following DAX formula to find the value in column B based on the filter selection in column A. When I have mulitple values in column B it returns blank; I want to return the first value.

column A (Selected)
07898

Column B
Apple
Apple

I want to return Apple as opposed to blank. Not sure how to use the EARLIER function in this if that applies.

Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),
                 VALUES(njrew_h_prmry_outcm[CUPDETAILS]),"")

I want to return only the first instance of a value where multiple are shown.

In this scenario, have you created relationships between the two tables? — Frostytheswimmer

Ellen S Ellen S · Accepted Answer · 2019-06-21T17:26:58

I would use FIRSTNONBLANK:

Cup_h_prmry = IF(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),
              FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS], 1),
              "")

This will return the first sorted value for CUPDETAILS. The second argument of FIRSTNONBLANK is not necessary in this case, and I have set it to 1.

find the first occurrence of a value based on a selection

1 Answers