cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## FIRSTNONBLANK with Multiple Values only returns value for one row

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

1 ACCEPTED SOLUTION
Helper I

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] )

2 REPLIES 2
Super User

@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]))``

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

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] )

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors