Forum Discussion

stacey's avatar
stacey
Frequent Visitor
2 years ago

Filter with empty rows

Hi PBI Community,

 

I'm new in Power BI and I'm struggling with a table.

It's a bit complex (for me :P) and I'd really appreaciate any guidance or help.

 

I have the table below. I want to keep the topics that are in a specific date period and the topics that are empty.

 

 

I need to:

- keep the topics that are between "2weeks before today and 2weeks after today"

- keep the topics that are empty i.e have no next milestone (like topic B)

- if topics include both empty fields and text fields, then keep only the text fields.

 

The end result should look like this:

 

 

My first thought was to calculate the difference between today-next date and limit the period with a slicer. That gives me the below result, but how can I remove the empty row from Topic A?

 

Diff Today-NextDate = DATEDIFF(TODAY(),'Table'[Next Date],DAY)

Any ideas?

 

Thanks

Need Help

10 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi stacey ,

    I am thinking those actually arent blank cells. But try this:

    =
IF (
    ISBLANK ( 'table'[NextMileStone] )
        || 'table'[NextMileStone] = "",
    BLANK (),
    DATEDIFF ( TODAY (), 'Table'[Next Date], DAY )
)
    • stacey's avatar
      stacey
      Frequent Visitor
      2 years ago

      Thanks for the reply.
      It gives the same result.

       

      • danextian's avatar
        danextian
        Super User
        2 years ago

        What does the calculated column show in data view? Please also show those colums in Power Query.

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    stacey I think where the issue, can you describe the logic why you are getting C and D in your result set (on your original post)

     

    - keep the topics that are between "2weeks before today and 2weeks after today"

    - keep the topics that are empty i.e have no next milestone (like topic B)

    - if topics include both empty fields and text fields, then keep only the text fields.

    • stacey's avatar
      stacey
      Frequent Visitor
      2 years ago

      Hi,
      C and D don't include next key milestones dates between the requested timeline  "2weeks before today and 2weeks after today".

      The idea is to still show these topics even if they don't include the milestones.

       

      Thanks