Inverse of FIRSTNONBLANK

[zudar]

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

[v-zhenbw-msft · Accepted solution]

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.

[zudar]

@v-zhenbw-msft 

Thanks for your reply.

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!

[Ashish_Mathur]

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.

[v-zhenbw-msft]

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.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

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.

[littlemojopuppy]

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

[zudar]

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?