Filtering many-many relationships not working

[afaro]

I have a Calendar table with the following years: 

Year	Quarter
2022	1
2022	2
2022	3
2022	4
2023	1
2023	2
2023	3
2023	4
2024	1
2024	2
2024	3
2024	4

I have a Sales table with the following data: 

Transaction	Region	Year	Revenue
A	W	2023	1
B	X	2023	2
A	Y	2023	4
B	Z	2023	3

My filter context in the matrix is region. 

I have a many-many relationship with relation with year column of Calendar with the year of Sales. 

I have enabled filtering only in one direction from Calendar to Sales. 

Sales doesn't have the rows for 2024 or 2022. 

When I put a slicer for year from the Calendar table, 

then the total revenue per region doesn't change no matter which year I select. It always gives me the total revenue for 2023. 

However, when I use the year from the Automation Forecast table as slicer,

it does give me blank for year 2022 or 2024 which is what is expected. 

Why is the year slicer from Calendar table giving me the wrong values? 

Thank you in advance. 

[PijushRoy]

Hi @afaro 

Please check the Year column datatype in both table 

when I use the year from the Automation Forecast table as slicer
Is the Automation Forecast table 3rd table in your data model, please check the filter

[afaro]

I made a typing error. 

The Sales and Automation Forecast table are the same table. Mistakenly used different names for the same table. I don't see an option to edit my post, I would have edited it now if not for that.

[PijushRoy]

Ok, no problem @afaro 

If the data type is the same for both tables, this should work.
Also check if you turn off interaction or not

If not solved, 
Please create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link in this thread. If you are using a spreadsheet to create/import the sample data instead of the manual import method, share the spreadsheet as well. 

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[afaro]

Changing the datatype didn't work but I added an intermediate year table with year values between the two tables and then it worked.