cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Benjamin_C Helper I
Helper I

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 Helper I
Helper I

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

View solution in original post

2 REPLIES 2
Community Support
Community Support

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, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Benjamin_C Helper I
Helper I

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

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors