PowerPivot to Return Matching Records

[brawnystaff]

I have a data in two different worksheets/tables that I am trying to match up the unique IDs and dates of service and return the corresponding claim number. One table lists a specific date in one column for unique IDs and the other table lists a date range in two columns for the unique IDs. You can check out a sample workbook here:

https://drive.google.com/open?id=0BweyQNyritHaX0FFRDRZVEpieUE

Column H in the worksheet "Physician Data" is the result I am trying to get in PowerPivot (along with data in worksheet Physician Data).

Currently, using some formulas to do the analysis (Index/Small/Row) but takes alot of time to do complete if the spreadsheet is large. As such, want to see if more efficient/speedier in PowerPivot. Any info is appreciated..

 

[brawnystaff]

Allright, I made some corrections to the workbook (wrong relationship). See link below:

https://drive.google.com/open?id=1cpoQqj51XmXWe-VOtTiySci5qJnvwtk-

Furthermore, I got the following code to concatenate the hospital ICNs on the physician table using the following:

=CALCULATE(CONCATENATEX(Values(Hospital[ICN]),Hospital[ICN],","))

The only issue it is showing all hospital ICNs when placed on Physician Data table. I need to limit to ICNs that occurred during the same timeframe as the date shown on the Physician Data table. I believe what I am looking for is as follows:

=DATESBETWEEN(PhysicianData[Date of Service],Hospital[AdmDate],Hospital[DischDate])

However, attempts to incorporate this datesbetween criteria with the concatenatex formula has not worked out. Any ideas as to what the problem is?