Forum Discussion
Power pivot
I created a data model in Power Pivot with two tables, the calendar table with data only on dates and variations in time measurements to meet the period referring to the data table and the data table with information about the entry of product loads into the my business.
The calendar table was imported into the data model and the main column, the one containing the list of dates, is formatted as a date and is the column that will be the primary key for the relationship with the data table.
The data table in turn also has a date column that is formatted as a date in the same way as the date column in the calendar table.
I created a relationship between these two tables, with the 1 part being the date column of the calendar table and the many part being the date column of the data table.
In the data table, the same load can be repeated in several lines, but with different product information. Therefore, several lines can have the same load identification number and the same date and the repetition of this data in different lines depends on the variety of products contained in the load.
With all this, it was expected that the relationship between the calendar tables and the data table would occur easily, but that was not what happened.
Using the pivot table feature to make associations of information there is no interaction.
Example:
If I insert the identification number of a load in the row field of the pivot table and also insert the data for the year of this load, the relationship will be displayed with the number of this load and with all the years in the calendar table on the side, which is illogical. as only the year of the loading date was expected to be displayed.
How can I activate the relationship between the calendar table and the data table for my case?
6 Replies
Nope, it's enough to build relationships based only on Date field. If in data table are actual dates, not datetime formatted as date.
The question is what such relationship for and how to use it. PivotTable ignores any relationships if no aggregation is added to values. My guess was that's why you have such result.
Perhaps other reasons, but without sample file or more details it's hard to say.
- Sergei, today I generated an aggregation of load weights in power pivot for data analysis using power pivot's pivot table feature. In the lines field, insert the information about the load identification number and in the values field the aggregation that adds up the weights of the loads. It worked, the relationship is correct. Thank you very much for your help and attention.
If your fields are dates it for the relationship it doesn't matter how fields are formatted. It shall be correct one-to-many and it's better to hide the field on many side.
In PivotTable if you have no aggregation it returns all fields from Raw part with full join, i.e. all values of the field again each value of another field. As soon as you add aggregation it shall work correctly.
- Sergei, thank you.
