get value based on dynamic column

[Minseven07]

hi all,

i want to get a value from a table based on a dynamic columns name. Here is the context:

my tables are linked this way:

here is an example of structure info:

as you can see, the column year is what i want to get.

the matrix on my report should looks like this:

i tried something like this:

VAR CurrentYear = SELECTEDVALUE('PGI_export_xls'[date].[Year]) // get the year

but then i'm stuck. I don't see how to get the value from the column_name = CurrentYear

there is a SWITCH method where i can test dynamically if the column name match the CurrentYear

CurrentYear = 2022, 'structure_info'[2022],

but that's not what i call dynamic, especially if i have many columns.

thx

[amitchandak · Accepted solution]

@Minseven07 , You should unpivot the year columns in the power query

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

[Minseven07]

I have tried this but i have unpivoted the years column and the id. Unpivoting just the years worked! You rock! Thanks