Report level date filter only applies when there are values? Live Connection

[Staregirl]

Hi all

***Edit - I answered my own question, which is there's no relationiship between the Policy table and the PolicyEffectiveDate table. I'm being daft.

If I want to use Policy[PolicyEffectiveDate] in a slicer because I want to only offer dates on which a Policy started, what's the best way?

***

Not sure if this is a Live Connection thing or not, but worth stating upfront I'm working on a Live Connection.

I have a Date table with contiguous dates Called PolicyEffectiveDate and another table, Policies, that has a [PolicyEffectveDate] field.

So Policy[PolicyEffectiveDate] - non-contiguous

PolicyEffectiveDate[PolicyEffectiveDate] - contiguous

I have a Fact table that has keys to both tables, from which I am getting LineEstimatedPremiumAllocation, which in my world is equivalent to 'Sales'.

I have a report-level filter using the PolicyEffectiveDate[PolicyEffectiveDate] from my date table, restricting it to the last 6 years.

If I put Policy[PolicyEffectiveDate] and 'Sales' into a table, the report-level filter works as expected. However, if I remove the 'Sales' field, the filter no longer applies. Same thing if I try to use it in a slicer.

It is a proper 'Date' - I get the Between, Before and After options on the slicer and all that.

Have posted some snips below, including of the tables in the model.

Thanks!

[v-yangliu-msft]

Hi  @Staregirl ,

Filters and slicers filter the data displayed on the report, not the data in the table, so the report-level filter is applicable regardless of whether there is a value

I think you can try the slice of after, which is from the date you choose to the end

The differences between the four types of relative date slice are as follows:

• Between: In the Date Slicer "Between" provides the range of dates between the start and end date.
• Before: When we select the Before option in the Date Slicer, the start date automatically grades out, which means we can get the report before the end date.
• After: When we click on the “After” in the date slicer pane the end date becomes stagnant so that we can fetch the results after the start date.
• List: List in the Date Slicer provides a list of all the date ranges so that we can select dates accordingly for the results.

This is a related link for your reference. I hope it can help you:

https://mindmajix.com/complete-guide-on-power-bi-slicers

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.