Greetings from the other side of the globe,
I am looking for a solution to the following issue. I have tried all things known to me, including asking various LLMs like ChatGPT, CoPilot, etc. The following thing.
I have two tables in the same worksheet with the following names:
Worksheet: FN_Base
Table1: tblFN_WL_BASE
Table2: tblFN_WL_BASE_RECTIFY
Table1 has the following columns. The first column "FN No - Year" is the primary key in the data model.
These are the columns of table1:
FN No - Year | Fn No | Year | FN Description | FN Status | FN Status Date
Data types are: Text | General | General | Text | Text | Date
These are the columns of table2:
FN No - Year | FN Description (short) | Rectification | Assigned to | Responsible | Remark
Data types are: Text | Text | Text | Text | Text | Text
In the data model (Power Pivot) the column "FN No - Year" (Table2 - Foreign Key) connects to column "FN No - Year" (Table1 - Primary Key). The relationship is 1:many. Entries in the column "FN No - Year" in both tables are as follows:
Table1: 0001-2024 ||| Table2: 0001-2024 ... no duplicates, same data type, by the look of it 100% identical.
To demonstrate my problem, let me show possible entries in the column "Assigned to"
Table2: FN No - Year: 0001-2024 | Assigned to: Company 1 | Responsible: James
Table2: FN No - Year: 0002-2024 | Assigned to: Company 5 | Responsible: Mary
Table2: FN No - Year: 0006-2024 | Assigned to: Company 10 | Responsible: Nobody
Table2: FN No - Year: 0250-2023 | Assigned to: Internal | Responsible: Team
The pivot table uses these two tables. In the ROW window, the first column takes the "FN No - Year" from Table1 as well as the column "FN Status". The pivot table is intended to use the following columns from the data model, all shown in the ROW window of the field list.
From table1: FN No - Year (= primary key) | FN Status | FN Status Date
From table2: Assigned to
However, instead of showing the "Assigned to" info for every "FN No - Year" (which should only be one), the pivot table lists ALL available entries (this is Company 1, Company 5, Company 10, Internal) for every FN No - Year.
What is the point at issue here and how can this be resolved? I have asked someone to try the same thing in PowerBI and it worked but only when the cardinality was set to 1:1. Which is not possible in Power Pivot, but since there are no duplicates in FN No - Year, I would have expected that a 1:1 relationship would be enforced.
Does someone has an idea how resolve this in Power Pivot / Data Model?
Any quick help is highly appreciated. Thank you so much.
Ralph
It looks like the issue arises from how the relationship is set up in Power Pivot. Even though you have a 1
relationship, Power Pivot may still be treating the "Assigned to" field as having multiple entries per "FN No - Year" due to the way the tables are structured or how the data is interpreted.
To resolve this, consider the following:
- Check for Hidden Duplicates: Even though you mentioned there are no duplicates, ensure that there are no hidden or unrecognized discrepancies (such as extra spaces) in the "FN No - Year" fields in both tables.
- Ensure Correct Data Relationships: Double-check the relationship settings in Power Pivot to confirm the foreign key is correctly mapped, and the relationship is set as 1
.
- Use DAX Calculations: You could also use DAX to create a calculated column or measure that ensures only one value from the "Assigned to" field is pulled based on the "FN No - Year".
Do you have a value field in the pivot table? If not, you will end up with what you describe.
Hello to both of you,
Let me start with the Velouria's reply. I do not have any value as there is no content which would require a calculation.
Regarding Wasim's reply. "Assigned to" can have multiple entries, yet my understanding is that the relationship between the two "FN No - Year" should sort this out. In order to ensure that this is understanding is (kind of) right, I have created a new table 2 with only one column (which is FN No - Year) and have run into the same issue. For every FN No - Year from table 1 the pivot table shows ALL entries for FN No - Year from the new table2.
It seems to me that the issue is connected with the relationship, but I do not know what to do differently from what I have already done.
Any more ideas?
Highly appreciated,
Drag something in the Value area anyway, as Velouria suggested. For instance the FN-Year filed. That's a text and will automatically set it to Count. Hide the column if you don't want to see it, but then the relationship shall work. No need for complex DAX in this case.
Riny, Velouria,
OMG, putting something in the Value field (I have put FN No - Year from table 2) resolves the issue.
Can you explain to me why this now works?
Thank you, happy greetings. 🙂
Well, a pivot table needs something to summarise/aggregate. If there is noting in the Values area, every combination of FN-Year, Status and Assigned company is possible. Until you put some value in a pivot table will not exclude the options that don't exist in the data set. Perhaps Velouria can word it better, though.
I think Riny has summed it up nicely. 🙂
If you come from a database background, relationships in data models are a bit weird. Unless you have a measure in use, the relationships basically have no effect at all, and you end up with a Cartesian product of your fields.
Thank you so much for all your clarification. You really helped me out here.
