+ Reply to Thread
Results 1 to 4 of 4

Power Pivot doesn´t create relationship

  1. #1
    Registered User
    Join Date
    03-10-2020
    Location
    South America
    MS-Off Ver
    2019
    Posts
    7

    Power Pivot doesn´t create relationship

    Hello, I´ve recently started discovering Power Pivot and I´ve found myself with a block. One sheet has dates and items, and the other one has the items with it´s brand. What I want to know is, in each month, how many brands are there.
    Whenever I try to obtain this data it asks for a new conection between the sheets and when I put the date in the rows field and the brand in the value field, and it doesn´t show me the correct numbers.

    Edit: I've posted the file having corrected one header and turned the ranges into tables. Still the same error and it won't work.
    Attached Files Attached Files
    Last edited by bacosta99; 03-24-2021 at 08:21 AM. Reason: New File

  2. #2
    Registered User
    Join Date
    03-23-2021
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Office 365 for Business
    Posts
    4

    Re: Power Pivot doesn´t create relationship

    First you need to define data converting each range to a table using 'From Table/Range' on Data tab or using 'Add to Data Model' on 'Power Pivot'. Don't forget to mark 'My table has headers'. Then create a relationship 'Sheet1.Brand' (which is Code actually) to 'Sheet2.Code'.
    Last edited by R37ht356; 03-24-2021 at 01:15 AM.

  3. #3
    Registered User
    Join Date
    03-10-2020
    Location
    South America
    MS-Off Ver
    2019
    Posts
    7

    Re: Power Pivot doesn´t create relationship

    It still doesn't work, maybe Power Pivot doesn't connect tables like that, is that the case?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Power Pivot doesn´t create relationship

    Seems to me that there should be an easier way however I feel that the following produces what you want.
    1. Add an implicit measure (Brand column) to Tabla1 using: =LOOKUPVALUE(Tabla2[Brand],Tabla2[Code],Tabla1[Code])
    2. The pivot table is then produced from Tabla1 with Date (mes) in the Rows area and the new Brand field in the Values area (Distinct count)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-21-2020, 07:51 PM
  2. Pivot diargam - create relationship
    By Addiaz in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-25-2019, 02:41 AM
  3. Power pivot relationship for date not working
    By AliAB in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 07-22-2017, 05:07 PM
  4. Replies: 0
    Last Post: 02-16-2016, 05:34 AM
  5. Replies: 0
    Last Post: 11-17-2015, 03:12 PM
  6. Replies: 0
    Last Post: 07-28-2015, 05:35 PM
  7. Unable to create meaningful relationship in Power Pivot
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-08-2014, 10:28 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1