Working with tables in powerpivot

I have three tables in powerpivot. The first is claims plus amounts plus dates, the second is unique claims, and the third is a calendar table. I want to add dates into the unique claims field and link it to the first table (I think). How can I do this?

Spreadsheet attached.

can't open your file, DK if it's only for me or for others too

Hi,

Create Pivot Tables from table1 instead of individual tables for "Claim" and "Date".
Please see attached.
As you will see I have added a few rows and when you "refresh" the Pivot Tables the data automatically updates.

Regards

peterrc

@MarvinP. Thanks, I'll take a look. I feel like this can be done in regular powerpivot haha. It's fairly simple, but not for me

Hi PeterCC. In my regular file, I use a calendar (date) table in order to be able to bin by date. I need to be able to do that. Are you saying just do it all in one table?

This might help me. I created the other table to calculate the average for UNIQUE claims based on a filter (in this case paid). Can I create this in a table where there are NOT unique claims counts? Example attached.

Essentially, how do I create a calculated column or measure that will give me the average (or sum) by customer without having to create a new table. The new table with unique claims IDs creates the problem because I don't know how to link it to the dates (which are in the date and original data file in the example I provided above. Thanks.

@MarvinP. I read through that article. It was very interesting but I'm not sure it solves my specific problem