Struggling with Basic relationship joins

malraff

New Member
Joined
Jan 25, 2017
Messages
2
hi

i have 2 really simple data models

data set 1

Code
1
2
3

data set 2

TopLevel desc
1 apple
2 banana
3 Carrot


i join the 2 table with a relationship Code = Toplevel, i have no duplicates

but when i use a pivot table to show the data i get

Code Desc
1 apple
banana
Carrot
2 apple
Banana
Carrot


im obviously missing something obvious to a trained eye?

any ideas?

mal
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,199
You only have 1 data model but 2 tables.

A pivot table is a tool to aggregate date, not a query tool. If you want to use a pivot table, you need a measure too. You could simply drag desc to values as well as rows
 

malraff

New Member
Joined
Jan 25, 2017
Messages
2
You only have 1 data model but 2 tables.

A pivot table is a tool to aggregate date, not a query tool. If you want to use a pivot table, you need a measure too. You could simply drag desc to values as well as rows
hi Matt, thanks for reply
i know i am am trying to use power pivot as a db, which would be nice for me in this instance
desc is a text field (description), if i put it in as a value then it wants to count/sum etc the field? where as i just want the text shown

mal
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,199
What version of Excel do you have? If you have Excel 2016, you can write this measure.

=concatonatex(values(table2[Desc]),table2[desc],", ")

if it is 2010/13 you can write this measure

=if(hasonevalue(values(table2[Desc])),values(table2[Desc]),"Multiple Results")
 

Forum statistics

Threads
1,089,191
Messages
5,406,724
Members
403,104
Latest member
mkirchner

This Week's Hot Topics

Top