Forum Discussion
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 `fecha_radicacion` field, and the second (calendar2) is connected to the `fecha_solucion` field. Both are active with a one-to-many relationship.
In the Power BI canvas, I have a slicer with the date from `calendar1`. This slicer is configured to accept only one date. The desired behavior is for the visual to show the seven days prior to the date selected in the slicer, and if no date is selected, to return the seven days prior to today. The measures section should include two calculations: the first is the count of rows in the fact table based on the `fecha_radicacion` date, and the second is the count of rows based on the `fecha_solucion` date. For example, suppose we have selected the date 10-02-2026, the visual should then display dates from 3-02-2026 to 9-02-2026. The first calculation will simply count the rows from the fact table, but the second calculation should count the rows from the fact table for the specific date, only by the date of the solution_date without being restricted by the filing_date.
I've attached an image of what I tried with keepfilters to intersect the dates, but it's not working.
I appreciate any help you can provide.
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] ) )
6 Replies
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 _fechasThis works because you're not relying on a relationship to return the user input and simplifies your DAX expression.
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.
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] ) )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.
Hi jcamilo1985
As what hnguy71 has suggested, one of the calendar tables has to be disconnected. Filters from a related or the same table will show only the rows selected. Functions that modify the filter context can only override the value being returned by a measure but not the visible rows.
Thanks for replying.
