Allow a slicer to change columns on a matrix.

[Anonymous]

Hello all, I have a seemingly simple issue that I'm having trouble implementing. I have a simple matrix where the rows are categories, the values are profit and %GT profit, and the columns are years. For the purpose of this question, I'm focusing on the years. 

The goal here is to have a slicer affect the number of years displaying. 

For example, if A value is chosen, display years 2020 and greater

if any other value is chosen, display all years. 

I did some googling and this seems possible, but all the examples I viewed were more complex and I couldn't retool it to my issue. 

Any help would be greatly appreciated, thank you!

EDIT: here is some sample data and the expected output: 
Data: 
Year_Field = 2015-2022
Segment_Field = A, B, C 

Category_Field = X, Y, Z

Expected output in Matrix form: 

No slicers chosen = 

Category  2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022
X
Y
Z

Slicer selection of A = 
Category  2018 | 2019 | 2020 | 2021 | 2022
X
Y
Z

Slicer selection of B and C = 
Category  2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022
X
Y
Z

[Anonymous · Accepted solution]

Hi @Anonymous ,

You will need to create a slicer table like below:

Then create a relationship between slicer table and fact table by using year columns.

Then you should be able to filter the matrix by slicer.

Best Regards,

Jay

[parry2k]

@Anonymous no idea how I assume these are columns in the same table:

Year_Table = 2015-2022
Segment_Table = A, B, C 

Category_Table = X, Y, Z

Regardless, can you paste data in the table format. 

[Anonymous]

@parry2k 

Yeah, no idea either, my post clearly says field! 😛

Seriously though, I'm not sure what you mean by table format, sorry. This is from 1 table with about ~20 columns. I would rather not post exact data and what I have structured already is the logic at its most basic. 

Can you elaborate a bit and I'll see if I can accomodate? 

[parry2k]

@Anonymous what is the relationship between these tables?

[Anonymous]

@parry2k These are not separate tables, just separate fields in the same table. 

[parry2k]

@Anonymous if you can put a sample data and the expected output, it will help to provide the solution.  It surely need some DAX juggle but doable.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k 
I have updated my original post to include a simple example. 

[Anonymous]

@parry2k  are you able to assist? 

Post is a bit older, so not sure if I should repost or wait for a reply...