Dynamic Complex Filtering with slicers

Hi,

I have solved a similar question in the attached file.

Hope this helps.

Hi There!

Please consider this solution:

 

Step 1: Create a disconnected Calendar table in your data model All the calendar references in these measures must be to the disconnected table

 

Step 2 Date Slicer: Use the Drop Down slicer style with your date field but add other fields to make selection easier. Add Year, Quarter, Month, Date to the fields section of the date slicer and this will allow you to pick a specific date.

 

Step 3 Measure to select the date:  Create a measure like this:

Selected Date = Max(Calendar[date])  --this grabs the max date in the selected context

 

Step 4 Add Date Measure:

AddOneMonth = 

VAR _DateSelected = [SelectedDate]
VAR _AddOneMonth = CALCULATE( DATEADD( Calendar[Date], 1, MONTH ), Calendar[Date] = _DateSelected )

RETURN _AddOneMonth

 

Step 5 Criteria Measure:

MeetsCriteria =
SWITCH(TRUE(),
Table[Fecha] > [AddMonthToSelected] && Table[Vencimiento]< [AddMonthToSelected],
1, 0
)

 

Step 6: Add this column to your table visual and use the filter pane to filter to the value you are desiring

 

1) Create a slicer table (disconnected)

Use a date table just for the slicer (it must not relate to the fact table):

SlicerDate =
CALENDAR ( DATE(2020,1,1), DATE(2035,12,31) )

Put SlicerDate[Date] on a slicer.

 

2) Create a measure that flags valid fact rows

InRange (row) =
VAR SelDate = SELECTEDVALUE ( SlicerDate[Date] )
VAR SelPlus1 = EDATE ( SelDate, 1 )
VAR Venc = MIN ( Fact[Vencimiento] )
VAR Fec  = MIN ( Fact[Fecha] )
RETURN
IF (
    NOT ISBLANK ( SelDate )
        && SelPlus1 >= Venc
        && SelPlus1 < Fec,
    1,
    0
)

 

3) Apply it to visuals

For each visual you want to filter:

• Drag InRange (row) into the visual-level filters

• Set it to is 1

This will make the visual only show records where:

SelectedDate + 1 month >= Vencimiento and SelectedDate + 1 month < Fecha

Hi Jan03 ,

Right now your slicer table is correctly disconnected, but your row-level measure is being evaluated in an aggregated context, so MIN() or SELECTEDVALUE() isn’t reliably pulling the current row’s Fecha/Vencimiento. That’s why it always returns false.

Try again with this measure:

InRange =
VAR SelDate = SELECTEDVALUE ( SlicerDate[Date] )

VAR SelPlus1 =
EDATE ( SelDate, 1 )

VAR RowVenc =
SELECTEDVALUE ( Fact[Vencimiento] )

VAR RowFecha =
SELECTEDVALUE ( Fact[Fecha] )

RETURN
IF (
NOT ISBLANK ( SelDate ) &&
NOT ISBLANK ( RowVenc ) &&
NOT ISBLANK ( RowFecha ) &&
SelPlus1 >= RowVenc &&
SelPlus1 < RowFecha,
1, 0)

For each visual you want filtered drag InRange into Visual-level filters and set it to is 1.

Hope this helps.
Thank you.

Hi Jan03 ,

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help.

If possible, could you also share a sample PBIX file (without any sensitive information) or some sample data that mimics your scenario? This will help us better understand your requirement and provide possible approaches.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

Hi Jan03 ,

Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.

If you have any further questions, feel free to reach out anytime.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.