saurabh726
Well-known Member
- Joined
- Dec 13, 2003
- Messages
- 891
Hi All,
I have been playing with power pivot for some time now and what i'm not able to establish is basically Join on 1 to n as power pivot only accepts 1 to 1 join. Basically what i mean is that if in my
Table-1
Field-1 Field-2 Field-3
Table-2
Field-1
Now Field-1 is equal to Field-1 to Field-3 of table 1 and i want to summarize them as shown in the attached file so that i can slice and dice the data the way i want to..
Book1.xlsx - Speedy Share - upload your files here
A working copy of the file is at above link..
Now in order to bypass that i build my table and used formulas to get my results and then building another table using Dax formulas which gives me the desired results what i want but it doesn't give me an additional functionality of drilling down like what i have in the view sheet when you change the value of I3. I know this is not their because their is no relationship in place and that's what i'm not able to crack is how to build this 1 to n relationship.
This i want to do with powerpivot only since the actual thing that i'm building is much more complex then what i have showed here and i can work out the rest of the logics once i fix this one.
Also i'm building on this on excel 2010 but if 2013 allows you to do this since there is an update on powerpivot model. I can work on that since i got 2013 as well.
Need inputs or thoughts about how to best accomplish this..
Saurabh...
I have been playing with power pivot for some time now and what i'm not able to establish is basically Join on 1 to n as power pivot only accepts 1 to 1 join. Basically what i mean is that if in my
Table-1
Field-1 Field-2 Field-3
Table-2
Field-1
Now Field-1 is equal to Field-1 to Field-3 of table 1 and i want to summarize them as shown in the attached file so that i can slice and dice the data the way i want to..
Book1.xlsx - Speedy Share - upload your files here
A working copy of the file is at above link..
Now in order to bypass that i build my table and used formulas to get my results and then building another table using Dax formulas which gives me the desired results what i want but it doesn't give me an additional functionality of drilling down like what i have in the view sheet when you change the value of I3. I know this is not their because their is no relationship in place and that's what i'm not able to crack is how to build this 1 to n relationship.
This i want to do with powerpivot only since the actual thing that i'm building is much more complex then what i have showed here and i can work out the rest of the logics once i fix this one.
Also i'm building on this on excel 2010 but if 2013 allows you to do this since there is an update on powerpivot model. I can work on that since i got 2013 as well.
Need inputs or thoughts about how to best accomplish this..
Saurabh...