Dynamic Complex Filtering with slicers

[Jan03]

Hello, I have a question.

I am learning Power BI and I want to implement dynamic filtering based on user interaction within the dashboard.

Specifically, I need the user to select a date from a list, and then have that selected date used internally to perform a calculation: adding one month to it.

After that calculation, the resulting date needs to be compared against two fields in a fact table: Fecha and Vencimiento. The logic should work as follows:

• The selected date plus one month must be greater than or equal to the Vencimiento field.
• It must also be less than the Fecha field. This would allow me to filter all records in the fact table that meet those conditions.

The idea is to show in different graphics all of the registers that satisfy the condition described before. I initially tried to accomplish this using slicers, but from what I’ve tested so far, slicers only filter directly by fields. In other words, I haven’t been able to create more complex or computed filters using slicers alone.

[cengizhanarslan]

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

[Jan03]

Thanks, but it doesnt work. I did exactly what you said. Probably i made a mistake in the medium steps. And the problem isn't the data.

[d_m_LNK]

I think the only issue is that your initial variable is potentially pointed to the calendar table that has a relationship to the fact table and not the disconnected SlicerDate[Date] table you created for the slicer.

[Jan03]

I cant see a relationship between tables. The calendar table (which creates the slicer) is apart.

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[d_m_LNK]

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

[Jan03]

Thats the thing i wanted, but im having trouble with the step 5. The column you described at the step 5 is always false. Probably i did one of the previous steps wrong, but the measure of the steps 3 and 4 with its respective value seems okay, their values update in real time when i choose one or other option in the slicer. 

[d_m_LNK]

Make sure that the correct comparisons are happening for the correct table.  Also I think I forgot something in the last measure:
MeetsCriteria =
SWITCH(TRUE(),
SelectedValue(Table[Fecha]) > [AddMonthToSelected] && SelectedValue(Table[Vencimiento])< [AddMonthToSelected],
1, 0
)