Results 1 to 4 of 4

Thread: Struggling with Basic relationship joins
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Struggling with Basic relationship joins

    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

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling with Basic relationship joins

    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
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    New Member
    Join Date
    Jan 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling with Basic relationship joins

    Quote Originally Posted by Matt Allington View Post
    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

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Struggling with Basic relationship joins

    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")
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •