I have the following data in two tables. I'd like to populate ColumnApps in Table1 with all the apps from Table2.  However the technique needs to be able to pickup a multi valued cell.  If there's no solution with a multi value cell how can I split the data out to get what I need?

Table1:

ColumnServer ColumnApps
------------ ---------------
ServerA Need to populate this, can be multi value
ServerB
ServerC

Table2:

ColumnApp ColumnServers
--------------- -------------------
App1 ServerB,ServerC
App2 ServerA,ServerB
App3 ServerA,ServerC

I have the following data in two tables. I'd like to populate ColumnApps in Table1 with all the apps from Table2.  However the technique needs to be able to pickup a multi valued cell.  If there's no solution with a multi value cell how can I split the data out to get what I need?

Table1:

ColumnServer ColumnApps
------------ ---------------
ServerA Need to populate this, can be multi value
ServerB
ServerC

Table2:

ColumnApp ColumnServers
--------------- -------------------
App1 ServerB,ServerC
App2 ServerA,ServerB
App3 ServerA,ServerC

I should have mentioned that table1 is in powerpivot within excel.  The data is initially pulled from sql.  However table2 is data from an excel spreadsheet.

powerpivot for excel is not support many2many relationship. use DAX formulas instead, see this sample:

http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx

KR

powerpivot for excel is not support many2many relationship. use DAX formulas instead, see this sample:

http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx

KR

Thanks, I've seen that page and attempted to recreate it but power view still throws the following.  I'm able to get server/software and server/app combos to display separately as seen in one of the screenshots below, just not all three data items at once.

• Edited by JonSykes 9 hours 40 minutes ago