cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
diegoleno Visitor
Visitor

Help with table relationships, several tables per year

Hi everyone.

 

I am pretty new to Power Bi and dont know how to proceed with the following:

 

I have 5 different tables for the past five years that include a column for Suppliers and another column for the country of each plant we procure from from each supplier like below:

 

2015
SupplierCountry
AZ
AY
AX
BZ
BZ
CJ
  
2016
SupplierCountry
AZ
AY
BX
BX
BJ
CJ

 

The suppliers vary among the 5 year period (there are new suppliers as well as others that dont show up from year to year) and the occurrence of them also varies (eg. we might procure from 5 plants one year and the next from 9)

I want to be able to create a report that shows the variations in ocurrence along the 5 year period for supplier/country as well as visualize the new suppliers and those that stop showing up in the records during the same period.

 

Any guidance will ber very much appreciated.

 

Thanks in advacne

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Help with table relationships, several tables per year

Probably the best approach is to add a "Year" column to each of your 5 tables with the appropriate value, then do an Append Merge operation to append all 5 tables into 1 table (you would then turn off data loading for the 5 original tables to avoid loading all the data twice), then it would be much easier to write generic measures that will do comparisons across the various years. 

View solution in original post

Community Support Team
Community Support Team

Re: Help with table relationships, several tables per year

Hi @diegoleno ,

 

You can create new calculated table using DAX below.

 

Table = UNION(ADDCOLUMNS('2015',"Year","2015"),ADDCOLUMNS('2016',"Year","2016"))

 

4.png

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Help with table relationships, several tables per year

Probably the best approach is to add a "Year" column to each of your 5 tables with the appropriate value, then do an Append Merge operation to append all 5 tables into 1 table (you would then turn off data loading for the 5 original tables to avoid loading all the data twice), then it would be much easier to write generic measures that will do comparisons across the various years. 

View solution in original post

Community Support Team
Community Support Team

Re: Help with table relationships, several tables per year

Hi @diegoleno ,

 

You can create new calculated table using DAX below.

 

Table = UNION(ADDCOLUMNS('2015',"Year","2015"),ADDCOLUMNS('2016',"Year","2016"))

 

4.png

 

 

 

 

 

 

 

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User III
Super User III

Re: Help with table relationships, several tables per year


@v-xicai wrote:

Hi @diegoleno ,

 

You can create new calculated table using DAX below.

 

Table = UNION(ADDCOLUMNS('2015',"Year","2015"),ADDCOLUMNS('2016',"Year","2016"))

 


You can do that. But then you have all the data loaded into memory twice. It's a better practice to do the unioning in Power Query and only have one copy loaded into memory.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors