Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

FIRSTNONBLANK with Multiple Values only returns value for one row

Hi All,

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

Table 1:

ThomasBHudson_2-1664841267964.png

 

Table 2:

ThomasBHudson_1-1664841221494.png

 

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

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

View solution in original post

2 REPLIES 2
amitchandak
Super User
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]))

 

 

if needed add

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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