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.

 

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

Thanks in advance.

Need Help

3 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    2 years ago

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