Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
2 years ago

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

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...
Need Help
StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
2 years ago

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's avatar
edhans
Icon for Community Champion rankCommunity Champion
2 years ago

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.