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.

Post Patron

## Inverse of FIRSTNONBLANK

Hi all,

I'm looking for a way to retrieve a blank value from a list of values, if there is a blank value in the list. Something like the inverse of FIRSTNONBLANK, so I'd like to get FIRSTBLANK.

I will use it in a formula like (new column in Table1):

``````Lookup Blanks =
CALCULATE (
FIRSTBLANK ( Table2[Value]),
FILTER ( ALL ( Table2 ), Table2[LookupID] = Table1[UniqueID] )
)``````

Any idea how to do this?

Goal is to create a sheet that checks if there are blank values that need to be filled in, no need to know which Table2[LookupID] it is about, just if there is something not filled out for a Table2[LookupID]. One Table1[UniqueID] can have multiple Table2[LookupID].

1 ACCEPTED SOLUTION
Community Support

Hi @zudar ,

We need to create two columns in Table2, then we can create the result column in Table1.

1. Create two columns to judge if the ID has a blank.

``````Judge blank =
IF(
Table2[Value]=BLANK(),0,1)
``````

``ifisblank = CALCULATE(MIN(Table2[Judge blank]),FILTER(Table2,Table2[LookupID]=EARLIER(Table2[LookupID])))``

2. Then we can create the result column in Table1.

``````Column =
VAR _sum =
CALCULATE (
SUM ( Table2[ifisblank] ),
FILTER ( Table2, Table2[LookupID] = Table1[UniqueID] )
)
RETURN
IF (
ISBLANK ( _sum ),
"No values found",
IF (
_sum = 0,
"Contains empty value",
CONVERT (
CALCULATE (
MIN ( Table2[Value] ),
FILTER ( Table2, Table2[LookupID] = Table1[UniqueID] )
),
STRING
)
)
)``````

Note: the type of the column is TEXT.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

6 REPLIES 6
Community Support

Hi @zudar ,

We need to create two columns in Table2, then we can create the result column in Table1.

1. Create two columns to judge if the ID has a blank.

``````Judge blank =
IF(
Table2[Value]=BLANK(),0,1)
``````

``ifisblank = CALCULATE(MIN(Table2[Judge blank]),FILTER(Table2,Table2[LookupID]=EARLIER(Table2[LookupID])))``

2. Then we can create the result column in Table1.

``````Column =
VAR _sum =
CALCULATE (
SUM ( Table2[ifisblank] ),
FILTER ( Table2, Table2[LookupID] = Table1[UniqueID] )
)
RETURN
IF (
ISBLANK ( _sum ),
"No values found",
IF (
_sum = 0,
"Contains empty value",
CONVERT (
CALCULATE (
MIN ( Table2[Value] ),
FILTER ( Table2, Table2[LookupID] = Table1[UniqueID] )
),
STRING
)
)
)``````

Note: the type of the column is TEXT.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

Post Patron

I see that I've could explained myself better. I've tried using your solution, but it's not exactly what I'm looking for. What I'd like to see is the following:

Table 1:

UniqueID

A

B

C

D

E

Table 2:

LookupID, Value

A, 1

A, 2

A, null
B, 2

C, 1

C, 3

C, 2

D, null

Table 1 with desired result column:

UniqueID, Result

A, Contains empty value!

B, 2

C, 1

D, Contains empty value!

E, No values found!

So Table1[Result] is a calculated column that returns 3 possible results (and in this order of priority):

• Firstly, if there are no matches in Table2, it gives 'No values found!'
• Secondly, if one of the looked up matching values is empty, it gives 'Contains empty value!'
• Thirdly, if all of the lookup up matching values are filled out, give the minimum value.

I hope this makes it more understandable. Thanks for your help!

Super User

Hi,

Write this calculated column formula in Table1

``Calculated Column 1 = if(ISBLANK(CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[LookupID]=EARLIER(Table1[UniqueID])))),"No value found",if(CALCULATE(COUNTBLANK(Table2[Value]),FILTER(Table2,Table2[LookupID]=EARLIER(Table1[UniqueID])))>0,"Contains blank",convert(CALCULATE(min(Table2[Value]),FILTER(Table2,Table2[LookupID]=EARLIER(Table1[UniqueID]))),string)))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @zudar ,

Do you mean getting the min value when the Lookup column is blank? For example, we need to show the 1 in three blank rows.

If yes, you need to create a measure.

``````Measure =
CALCULATE(MIN(Table1[value]),Table1[Lookup]=BLANK())
``````

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Champion

First step would be to find all those with blank values.  From there find the first value you're looking for (it's not exactly clear what that is).

Post Patron

Not sure what you're getting at.

Right now, I'm using:

``````Lookup =
CALCULATE (
MIN( Table2[Value]),
FILTER ( ALL ( Table2 ), Table2[LookupID] = Table1[UniqueID] )
)``````

However, this measure fails to inform me if there's a blank Table2[Value] for a single Table1[UniqueID].. it just returns the lowest number (using MIN(...)). I want to prioritize the blank value, and after that show the lowest number.

Does that make sense?

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