cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors