cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Benjamin_C Regular Visitor
Regular Visitor

Use one path or another between tables depending on a SELECTEDVALUE

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

Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Benjamin_C Regular Visitor
Regular Visitor

Re: Use one path or another between tables depending on a SELECTEDVALUE

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

2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Use one path or another between tables depending on a SELECTEDVALUE

HI @Benjamin_C,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Benjamin_C Regular Visitor
Regular Visitor

Re: Use one path or another between tables depending on a SELECTEDVALUE

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