Filter with empty rows

[stacey]

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

[parry2k]

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

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

[danextian]

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]

Thanks for the reply.
It gives the same result.

[danextian]

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

[stacey]

[danextian]

Those blank lines probably aren't null. Power Query would show if they are. If they arent null, I'd replace them by right clicking on an empty cell, click replace value, leave the value to replace as is and enter null in as the new value.  I'd do that for all columns. Alternatively and assuming that the blank cells don't have any length, you can modify your formula to this:

=
IF (
    LEN ( 'table'[NextMileStone] ) < 1,
    BLANK (),
    DATEDIFF ( TODAY (), 'Table'[Next Date], DAY )
)

[stacey]

Thanks for the reply. Unfortunately it doesn't work. 
I'll try  to find some other workaround.

[danextian]

You still have not posted a screenshot of your data in power query

[stacey]