Calendar slicer not being affected by other slicers

[rafacastro]

I have a Calendar table created in the Power Query, using M. One row for each day, just like a normal calendar table. And I have a table called MJE with the posting date. I created the relationship between them, 1 (calendar) to many (MJE). I added a slicer with the year, but when filtering another slicer with data from the MJE table, nothing happens with the 'Year' slicer, the interaction is correct, the relationship is active. The other way is woking, when I filter the Year, it affects the whole report

In this image, if I filter 2023, I can only see 6 regions

But if I filter another region, I can see all years (selected a region that hasn't 2023 data)

My Language M table

My relationship

The interaction

If someone can help me, thanks

[johnt75 · Accepted solution]

The date table isn't being filtered because the relationship is one-way, i.e. filters flow from the date table to the rest of the model, they do not flow from the rest of the model to the date table. This is correct behaviour and you shouldn't change that relationship.

Instead use a measure as a filter on the slicer, to show when the value is not blank. You can use an existing measure that only returns values when a valid year is selected.

[johnt75 · Accepted solution]

The date table isn't being filtered because the relationship is one-way, i.e. filters flow from the date table to the rest of the model, they do not flow from the rest of the model to the date table. This is correct behaviour and you shouldn't change that relationship.

Instead use a measure as a filter on the slicer, to show when the value is not blank. You can use an existing measure that only returns values when a valid year is selected.

[rafacastro · Accepted solution]

Thanks

I added this measure to my calendar table and it worked

DatesWithData =
VAR RecordCount = CALCULATE(
COUNT(MJE[Posting Date]),
RELATEDTABLE(MJE)
)
RETURN IF(RecordCount > 0, 1, 0)