Use one path or another between tables depending on a SELECTEDVALUE

[Anonymous]

Hi,

- I have a basic one-column table  with years (2017 or 2018), and using a Slicer I can select the Year I want to refer to(using SELECTEDVALUE).

- I also have 2 tables of locations (offices, plants...) of my company: one list of locations for 2017, and another one for 2018. Each location is linked to a region and a division (there's a colum 'region', and a column 'division' in addition to the location ID (LID)), but these may change (well, mostly the division!) based on reorganisation in the company....

- On one 'side' on my 2 tables (Locations'17 and Locations'18), I have a Region table, and a Division table and I use this tables to filter my reports, calculate formulas including region and division parameters... 

- On the other side of my location tables, I have a list of projects by LID, one LID having eventually several projects. Each projects have attributes (savings, costs...), and my goal is to calculate these attributes by region/division...

Is there a way to say in essence "based on the year SELECTEDVALUE, use this location table or this one"? Or should the Locations tables be appended into one unique one, with a column for the year? I couldn't succeed either way...

Thx for your inputs,

Ben

[v-shex-msft]

HI @Anonymous,

I don't think you can dynamic control active relationship based on slicer.

In my opinion, if there tables has similar structure, you can use union function to merge 'Locations 17' and 'Locations 18' tables and add year tag, then build relationships between merge table and dim tables(region,division,year).

After these steps, you can simply use year to filter on project table.

Sample:

merged =
UNION (
    ADDCOLUMNS ( 'Locations 17', "Year", 2017 ),
    ADDCOLUMNS ( 'Locations 18', "Year", 2018 )
)

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @v-shex-msft,

Althought I used a slight different path (putting both files on the same directory, then get data by folder, then appending both files and using the file name to add the year), the result was the same as yours, but yes, I like your way too!

Thx,

Ben