Filter - now Date but I want Year

[mrchonginhk]

I have a Matrix table.
There are two Tables, one Fact and another Date and I already linked up so SAMEPERIODLASTYEAR is working
I have a problem with Filter Visual in which I want to put 2016, 2017, 2018 buttons which are the three years in FACT table.
However, the date in FACT table is date not year so they appear as 3 Jan 2016 etc instead of 2016.
My Date table has Year column but it goes to 2025.
I tried dragging Date Table's Year to Filter Visual and the buttons are 2016, 2017,... 2025, more than what I expected.
I only want years that is related to data in FACT table only, ie, only 2016,17 & 18

What should I do ? Create One more Column of Year in FACT table query ?
Is there another way we can use Date table to achieve the same ?
Thanks

 

[VBA Geek]

You can create an additional table containing just the years found in the fact table:

=<br><span class="Keyword" style="color:#0070FF">SUMMARIZE</span><span class="Parenthesis" style="color:#969696"> (</span> FactTable, 'Date Table'[Year] <span class="Parenthesis" style="color:#969696">)</span><br>

then link this table via a one to many relationship with the Date Table using the Year. Then use the year column in this new table in the slicer. In this case you would hide the Year column within the Date Table:

Or you can limit the rows of the date table to only the years found in the fact table during the ETL process