Hi All,
Excel 2010 V2
I have a database for healthcare patients where the data includes new patients on each row and demographics, account charges and payments along with physicians in the columns for each row. Extracting data and building pivots is fairly easy. The complex part I have been having trouble with for some times is that each account records includes 10 physician columns on each record. You have an attending physician, admitting physician, surgeon, consulting, etc. for each patient.
Now if I wish to report cases or charges by one of these physician fields it is very straight forward; however, I have a situation where I would like to build various reports on an given physcian that may appear within any of these 10 field for each record. (Similar to the function of excel, where I could have written an IF statement to look at these ten fields and return a value if one or more of the fields matched my cirteria.) Meaning, I want the pivot to return the number of case for say, Dr. Jone if he was a physician serving the patient within any one or more of theses 10 column fields. I know I can build a measurement to and use a calculate function to check each column and return a result if true, yet I perfer to build this same ability into a power pivot where the "user" can select a slicer to change which doctor or group of doctors they wish to report on. In a sense I want a dashboard feel where the user can select the physician and the pivot table updates.
I am not sure how to achieve this with a slicer as I do not think it is possible have a slicer of data validation field update the formulas of a measure to change the pivot results.
This is a litle difficult to explain, so I hope it makes sense. I have been struggling for months to create a basic user dashboard feel for this situation.
Any suggestions would be helpful.
Thanks
Terry
Excel 2010 V2
I have a database for healthcare patients where the data includes new patients on each row and demographics, account charges and payments along with physicians in the columns for each row. Extracting data and building pivots is fairly easy. The complex part I have been having trouble with for some times is that each account records includes 10 physician columns on each record. You have an attending physician, admitting physician, surgeon, consulting, etc. for each patient.
Now if I wish to report cases or charges by one of these physician fields it is very straight forward; however, I have a situation where I would like to build various reports on an given physcian that may appear within any of these 10 field for each record. (Similar to the function of excel, where I could have written an IF statement to look at these ten fields and return a value if one or more of the fields matched my cirteria.) Meaning, I want the pivot to return the number of case for say, Dr. Jone if he was a physician serving the patient within any one or more of theses 10 column fields. I know I can build a measurement to and use a calculate function to check each column and return a result if true, yet I perfer to build this same ability into a power pivot where the "user" can select a slicer to change which doctor or group of doctors they wish to report on. In a sense I want a dashboard feel where the user can select the physician and the pivot table updates.
I am not sure how to achieve this with a slicer as I do not think it is possible have a slicer of data validation field update the formulas of a measure to change the pivot results.
This is a litle difficult to explain, so I hope it makes sense. I have been struggling for months to create a basic user dashboard feel for this situation.
Any suggestions would be helpful.
Thanks
Terry
