Power Pivot slicing and dicing with count

[saurabh726]

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...

 

[SimonNU]

Your data appears to be poorly organised. Data should be transformed prior to hitting Power Pivot otherwise you'll experience lots of headaches.

Power Pivot does support one-to-many but not joining on multiple fields. The way around this is to concatenate or unpivot these fields.