Hi everyone,
My data is like: (no date info, only "year" and "period" column)
| Year | Period | ColA |
| 2021 | JAN | 13 |
| 2021 | FEB | 14 |
| 2020 | JAN | 11 |
| 2020 | FEB | 12 |
| 2019 | JAN | 9 |
| 2019 | FEB | 10 |
| 2018 | JAN | 7 |
| 2018 | FEB | 8 |
| 2017 | JAN | 5 |
| 2017 | FEB | 6 |
| 2016 | JAN | 3 |
| 2016 | FEB | 4 |
| 2015 | JAN | 1 |
| 2015 | FEB | 2 |
And the requirement is when user choose "2020" in year slicer, followings are displayed in a Matrix Table
| Year | ColA |
| 2020 | 23 |
| 2019 | 19 |
| 2018 | 15 |
Everything is the same except data of last 2 years are also included
Background:
- There are many other slicers and visuals for single-year result. Only this table will be for 3 year data. And user just want it follow year slicer (single select) automatically.
Approach:
Disable the interaction of this table with year slicer and then implement workaround.
There are many other filters so we are looking forward to some smart way to do it without writing a big "Calculate" table which is hard to maintain.
Firstly we think it will be easy but still can't make it after spending lots of time.
Tried some of following but still failed:
(1) Limit slicer based on another slicer in Power BI - YouTube
Use a hidden slicer (multiple slection) to control target matrix table.
However only displayed values of hidden slicer are updated. Can't achive select/de-select values automatically
(2) https://youtu.be/YeI_n8myE68?t=1364
Using simple DAX to filter year column only. As a new powerBI user, it's surprising to find such filter only works for assigned value instead of variables. Thus still no solution for our report.
Example PBIX file
Does anyone has any idea to do it? Thanks so much!
