Powerpivot dont recognice one to one relationship

[Jorge Manrique]

Hello everyone


PowerPivot does not recognize the relationship I am saying


The relationship must be one to one, but recognizes it as one to many.


I'm working with Exchange factors for different days.


I have this other factor the value of the exchange for the date specified in the above table

[/URL][/IMG]

This table shows the information I have the date of the exchange factor

[/URL][/IMG]

and the relationship is

[/URL][/IMG]

this should give me a value of one to one, but the PowerPivot recognizes it as a relationship of one to many, I get a ratio of one to many in the PivotTable

[/URL][/IMG]

someone has happened something similar and I can help, thanks

 

[scottsen]

Power Pivot does "support" 1:1 relationships, but really they are 1 to many. I suspect you are just using it the "wrong direction".

On your relatioship for FactorUSD -> FechaFactorUSD... notice the "dot" (many) is on the Fecha side. So, the filters want to "flow" from FactorUSD to FechaFactorUSD. You would want to make sure that, when putting rows/columns/slicers/etc on your pivot table report, you are using values from FactorUSD NOT from FechaFactorUSD, because the relationships aren't setup to flow filters that direction.

Of course, you can change the direction if its a truely 1:1, but then... you may have the same problem, just in reverse.

If it is truly a 1:1, you might improve your life by just combining the tables into 1.

 

[Jorge Manrique]

thank you very much for your answer scott
The problem I have when I create PivotTables from this configuration is that a single date should be one factor of change dollars and that is partly true but PowerPivot does not understand to look for a single value but shows me whole group date by date, see the image, i have one value of Factor_USD_Key but the powerpibot combine it with all the other values from the same Key in the other table, rather than just the value who make the macth, that because i said have a relationship 1 to many and not the 1:1 i need.

[/URL][/IMG]

You're absolutely right to suggest that this would be easier if everything in a single table, but this fragmented information I obtain in this way from the database.

 

[scottsen]

Oh, I *think* I see what is happening here. Can you add a measure/value on your pivot table?

By default, Excel will remove rows where the values are all blank in the pivot table. However, if you don't HAVE any values, it shows them ALL.

 

[Jorge Manrique]

yes i can add measure/value but the powerpivot dont recognize the unique value related, it show all the set of values and all of them are in blank except the description who really have the value, that because i said the powerpivot dont understand that exist a relation between two unique values, im thinking is because the key i using are dates and my problem is associted to the format...

 

[scottsen]

Can you share the workbook with me? (dropbox or similiar?)

 

[Jorge Manrique]

Can you share the workbook with me? (dropbox or similiar?)

Sure! this is the file, i sent you a mesage with the link, thanks for your help!!

thanks

 

[Jorge Manrique]

Hello again, I found a link where Rob Collie can explain what my problem is, it seems that I want to use a relationship to find a numerical value between multiple tables, but seems it just this works when you have information such as the name of a client, in my case I use the function Related to one of the tables for the sought value in the other table. Here is the link for anyone with the same certainly understand what I'm explaining. If there is any other way thank you very much for sharing.

“Relationship may be needed?” But I already have a relationship! What’s going on? - PowerPivotPro

thanks!!