FIRSTNONBLANK with Multiple Values only returns value for one row

[Thomas-B-Hudson]

Hi All,

I have two tables, both of which have multiple values.

Table 1:

Table 2:

Table 2 indicates whether a candidate has a placement tenure ending in the next 2 weeks, each candidate only has 1 row indicating this, any other rows for prior tenures are marked as blank.

What I am hoping to achieve in Table 1, is for all rows of this candidateID to have a value of 1, if they have a placement ending in 2 weeks in Table 2.

However with the formula I have used in Table 1, only 1 row is being tagged whereas the other row is not.

Is anyone able to assist in this?

Thanks,
Tom

[amitchandak]

@Thomas-B-Hudson , The first one should give value in both rows

Try Min

example

Column = CALCULATE(Min(Table2[Val]), FILTER(Table2, Table1[ID] = Table2[ID]))

if needed add

Column = CALCULATE(Min(Table2[Val]), FILTER(Table2, Table1[ID] = Table2[ID] && not(ISBLANK(Table2[Val]))))

[Thomas-B-Hudson · Accepted solution]

Hi @amitchandak ,

I wasn't able to get the desired results through these two calculations.

However I did find another calculation you have in a community thread which I tried and is working

= maxx(filter(table, [itemname] = earlier([itemname]) && [description] <> blank()), [description] )