Using a different table for relationship based on a SELECTEDVALUE

[Anonymous]

Hi, here's where I'd need your help...

- I have 2 tables (lists of Locations (plants, offices, identified as LIDs) in my company: one list of 2017 locations, and one for 2018 locations. Each location refers to a region (Europe, Asia..) and to a Division in my company (which can change from one year to another).

- another independant one-column table with 2 values (2017 and 2018), allows me through a slicer to select the year I want to refer to in all my formulas/calculations/measures. In other words, selecting 2017 in this list should calculate measures based on the location list of 2017.

- on one side of the 2 locations lists (Locations'17 and Locations'18), I have a table listing the divisions, and another one listing regions.

- on the other side of these lists, I have a table of projects, each project being linked to a single LID. Values likes savings, and costs are also linked to each project.

the dataset looks like this:

I cannot suceed to use the SELECTEDVALUE of the Year table to say "based on this value, use this table (Locations'17) or this one {Locations'18)"... Any idea on how to proceed? I thought about appending both locations tables and adding a Year attributes, but couldn't figure it out...

Can you please advise?

Thx in advance,

Ben

[v-chuncz-msft]

@Anonymous,

You may use Append Queries (Table.Combine) in Query Editor or UNION Function (DAX) to create a new table.

[Anonymous]

@v-chuncz-msft,

Thx for your feedback. This was my initial idea as well, however it didn't seem to work as I wanted... But as you seem to say it's the most logical way, I'll reinvestigate that option...

[Anonymous · Accepted solution]

That was the right path... but not an easy one for me: I appended both locations tables into one, adding a Year column to the appended table and getting rid of duplicates LIDs. But then I had a many-to-many relationship between my location table and my project list table, so I had to create a one-column table with LIDs as a bridge table between the Location table and the Project list, allowing a both way relation between this bridge and the Project list... But it now works nicely! 😉

Thx again for your help @v-chuncz-msft