Forum Discussion

jcamilo1985's avatar
jcamilo1985
Helper III
5 months ago
Solved

cancel slicer dates

First of all, thank you so much to everyone who takes the time to read this post. I have the following scenario: two calendar tables. The first (calendar1) is connected to the fact table via the `fe...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    Step 1) Helper: Selected date (or today) and the 7-day window

    _Selected Date =
COALESCE ( SELECTEDVALUE ( calendar1[Fecha] ), TODAY() )

_In Window =
VAR Cutoff = [_Selected Date]
VAR StartD = Cutoff - 7
VAR EndD   = Cutoff - 1
VAR ThisD  = MAX ( calendar1[Fecha] )
RETURN
IF ( ThisD >= StartD && ThisD <= EndD, 1, 0 )

    Put "_In Window" in the visual-level filters and keep only = 1. This makes the table show only the 7 days prior.

     

    Step 2) Count by fecha_radicacion (calendar1)

    Orders (Radicacion) =
VAR Cutoff = [_Selected Date]
VAR StartD = Cutoff - 7
VAR EndD   = Cutoff - 1
RETURN
CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( calendar1[Fecha] ),
    DATESBETWEEN ( calendar1[Fecha], StartD, EndD )
)

     

    Step 3) Count by fecha_solucion (calendar2) without being restricted by filing date

    Orders (Solucion) =
VAR DatesOnAxis = VALUES ( calendar1[Fecha] )
RETURN
CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( calendar1 ),
    TREATAS ( DatesOnAxis, calendar2[Fecha] )
)

     

hnguy71's avatar
hnguy71
Super User
5 months ago

Hi jcamilo1985 

 

I suggest you disconnect your second date table similar to this:


Then, on your page slicer or filter pane, use the date from the disconnected table to retrieve user input. So your updated measure would look something like this instead:

__prueba =

// return the user's selected date
VAR _filtrado = SELECTEDVALUE('Select Dates'[Date])

// return data based on date range
VAR _fechas = CALCULATE(YOUR_MEASURE_OR_EVALUATION, calendario[Fecha] >= _filtrado - 7 && calendario[Fecha] < _filtrado)


RETURN

_fechas

 

This works because you're not relying on a relationship to return the user input and simplifies your DAX expression.

jcamilo1985's avatar
jcamilo1985
Helper III
5 months ago

Thanks for replying.
I didn't get a chance to test this solution; Copilot marks it as the solution path.
However, what I did to work around it was create an additional calendar table, really just the date column. I linked this table using an active relationship to the filing date and an inactive relationship to the resolution date. I use these dates in the visual editor, then in DAX, with the active context of the main dates table, I create a small table with the dates I need. Then, using CALCULATE, I remove the filters from the other tables and pass the previously created table, and voila!

The new calendar table is small, so it doesn't impact storage or performance.