Check for Blank Rows after certain number of rows. EXAMPLE FILE INCLUDED

[StuartSmith]

I am trying to figure out how to check if there are any blank rows after a certain number of rows, but cant figure out the correct "IF" statement

Can someone point me in the right direction.

Exampe File: Check-for-Blank-Rows.pbix 

Thanks in advance.

[edhans]

I think you want something like this:

Test =
IF (
    numberofdaysabsent > numberofdaysrequiredby
        && CALCULATE (
            COUNTBLANK ( 'Absence Master list'[Backfill] ),
            'Absence master list'[absencedate] > lastrequiredbydate
        ) > 0,
    "Blank",
    "No Blanks"
)

If the absent days > days required and there are blanks, then return "Blank" otherwise "No Blanks" 

[StuartSmith]

Thanks.  I tried your code, but if any of the records has a "Blank" row, insead of displaing "Blank", it displays 2 rows, one saying "Blank" & one saying "No Blanks" and I cant figure out why. 

Thanks in advance.

[edhans]

It is because CALCULATE is invoking context transition and it is effectively iterating through the rows.

Test =
IF (
    numberofdaysabsent > numberofdaysrequiredby
        && CALCULATE (
            COUNTBLANK ( 'Absence Master list'[Backfill] ),
            'Absence master list'[absencedate] > lastrequiredbydate,
            REMOVEFILTERS (
                'Absence Master list'[Backfill],
                'Absence Master list'[absencedate]
            )
        ) > 0,
    "Blank",
    "No Blanks"
)

That will remove the filters for the backfill and absencedate fields and do the COUNTBLANK() on both at once. That may or may not be right and you will need to tweak those based on the fields in your visual.