Dynamic Date Slicer Based on Parameter Choice and Filter

[Evogelpohl]

I know this issue has been discussed several times, but I thought I would take another crack at it, with a simple example PBIX.

My example PBIX is here: Need Dynamic Date Slicer

Need: I have a report-page that allows users to choose if they want to see the full sales dataset or just the last six months of sales.  That logic works fine.  However, if they choose to filter on just the last 6 months, then the Date Slicer (that lets them fine-tune their range even further) should update/respond logically.

Problem: Understand the fundamental issue is that PowerBi doesn't re-calculate Calculated Tables based on filter selection.  They only calc upon refresh, as I've tried to create a SUMMARIZED or CALENDAR Calc'd Table, joined, to be used as the slicer-date *IF* a user has selected Yes or No on the 6-month parameter.

Has anyone else come up with a way to present a user with a "Smart Date Filter That Responds To Filter Changes" - as if the Date Slicer is included in the measure being activated - like a Table would respond?

Thanks.

[v-cherch-msft]

Hi @Evogelpohl

It seems you may have a look at this article about How to filter slicers dynamically.Below is a workaround in query editor.

https://community.powerbi.com/t5/Desktop/Dynamically-changing-Date-slicer/td-p/357593

Regards,

Cherie

[Evogelpohl]

Thanks, @v-cherch-msft.  I did read that post by Gilbert Quevauvilliers.  It's a similar pattern, I see what he's doing with creating a second FY filter table to put next to a date-slicer, and it works - selecting a date range changes the FY periods slicer.  However, if you take his FY slicer & choose "2019", (my) users expect the date slicer to re-adjust to only show dates within 2019 (1/1/19-12/31/19) - and it doesn't.  I've tried to cross-filter all tables, same.

Re; making a table in PQ.  Again, that'll only run on import.  I'd like the date slicer to be dynamic within the context of the resulting filters applied to the calendar table.  

[Anonymous]

It currently is a limitation to use relative date slicers with a dyanmic query parameter. 

The dynamic query paramter is a fairly new feature in 2022 but at least its acknlowedged by the Power BI team.

You can see the limitations here:

• Unsupported filters:

  • Relative time slicer or filter
  • Relative date
  • Hierarchy slicer
  • Multi-field include filter
  • Exclude filter / Not filters
  • Cross-highlighting
  • Drill-down filter
  • Cross drill filter
  • Top N filter