Search multiple columns for unique data elements to ultimately report on

[tbobolz]

Hi All,

I have been struggling with how to accomplish the below request. I can achieve the same desired results with multiple databases; however, if I can do it with one I could cut my file size by 70%.

I have a database of patient records that includes 5 physician type columns. (Attending, Admitting, Referring, Surgeon & PCP). Each unique patient row has all 5 of these fields, but they may not all be populated. I'd like to achieve two results.

1- Create a lists or database of physicians from any of these 5 columns and ultimately allows me to build a slicer to pivot my report on.

2- With the above mentioned slicer, when I select a provider, I would like it to select any rows that has the selected provider in any of those 5 physician type fields.

Ultimately, I would like to select a physicans name, and have it return row results if that physician is listed in any of the 5 fields (or listed in multiples fields each row) of any row, but only return unique row items in my report and tables. Such as charges, or counts of row items like patient diagnosis and other demographics.

I hope that makes sense, I have been struggling with this for a long time.

Thanks in advance for any help you can offer.

Terry

[v-ljerr-msft]

Hi @tbobolz,

In addition, for the detailed steps about how to Pivot and Unpivot with Power BI, you can refer to this article. 

Regards

[tbobolz]

Hi, Thanks for the reply and suggestion. This is an interesting feature that I did not know about.

After playing with it, I see that it combines or pivots my 5 columns into one column by duplicating each row. This would be perfect for the example provided; however, in my case, it takes one patient's account and duplicates it for every physician on the account. In my case the same physician can be in multiple column on the same patient record. Now when I pivot, it will duplicate the patient’s charges for every field that physician might be listed in. In one example, my physician is listed as the attending, admitting and surgeon on the same record, so my financials are now 3 times higher. I need to find if the physician is listed anywhere with-in these fields and return only the original "rows" or record’s data for an accurate look at that patient's financials.

Also I noticed that once the “unpivot column” is created, those 5 fields are removed. I already have other financial cuts based on those 5 columns. I have a dashboard create for each of those physician types already in my report. This way the user can view the physician business as just the attending, admitting, referring, surgeon or primary care physician. I now need a separate cut that looks at all those fields together and returns financials if they exist in any of those fields without duplicating.

I know this is a big ask and I appreciate you helping me learn. Currently I have this report built in Power Pivots, where I have two databases. one with non-reoccurring data that list each patient individually by record/row and it has each of these 5 physician types in each record/ row. Then I have a dataset similar to what the "unpivot column" achieves, multi-occurring financials. I have a relationship built to allow me to select the physician if it exists in the reoccurring data, but returns financials from the other dataset, as to not duplicate financials.

I could recreate this in the Power BI, but was hoping to learn a better way to manage data and eliminate the extra data if possible.

Thanks again for any help you can offer, I’m learning more every day!

Terry

[MarcelBeug]

My suggestion would be to unpivot the 5 columns in Power Query, so you get a table with fields:

Patient

Physician Type

Physician

and proceed from there.