Select multiple items from different slicer - without limiting other columns

[tbobolz]

I kind of posted this before, but from a different perspective that I could not solve.

I have a report connected to a data source that I cannot modify. Each row, or (record) has 5 physician fields. Admitting, Attending, Referring, Surgeon and PCP. 

Is it possible to have 5 filters, one for each of these physician fields, so that the user can select the SAME physician from each of these filter so the report returns records if the physician is listed in any one of these field within the data?

Or is there another way to return data for the same physician if he/she exits in any one of these fields? I cannot utilize the "ungroup column" as it is connected to a cube I cannot access.

I hope I have explained this well.

[Greg_Deckler]

Can you post your sample data? Seems like you really should unpivot those 5 fields.

[tbobolz]

I want to create a matrix or similar visual that returns the results shown at the bottom of this reply. If "John" is selected by the user in some manner, the results find and returns "John" in any row or column that his name appears. If it appears more than once per row, like in the first row where it appears 4 times in the same record, I want the result to only count the Cases, Charge, Payment and Net Revenue, once per line.

I meant to say Unpivot in my earlier post. However, this is not an allowed option with my data connected to a SQL Server Analysis Service Database, that I do not have access to modify. I attempted the unpivot in a model where I import the raw data myself; however, it seems to multiple the financial with unpivot, but I might have done it incorrectly.

Anyways, I perfer to leave it as a SQL model since the data is 10 million rows.

Thanks again for looking.

Entity	Patient Acc #	Admitting Phy	Attending Phy	Referring Phy	Primary Care Phy	Surgeon	Month	Year	1-Cases	Charges	Payments	Net Revenue
Hospital East	200	John	John	Bob	John	John	9	2018	1	37008.41	14987.00	14987.00
Hospital East	201	Mike	Mike	John	Mike	Dave	7	2018	1	25009.33	6147.20	7684.00
Hospital East	203	Sam	John	Mike	John	Wendy	9	2018	1	18506.36	4504.01	5630.00
Hospital East	204	Sam	Mike	Sally	Mike	Wendy	10	2018	1	9648.41	3170.00	3170.00
Hospital East	206	John	Sam	Suzie	Sally	Lori	1	2018	1	20087.81	3265.00	3265.00
Hospital West	207	Sam	John	John	John	Wendy	5	2018	1	18946.98	0.00	5844.24
Hospital West	209	John	John	John	John	Lori	3	2018	1	12020.32	5893.00	5893.00
Hospital West	210	John	Mike	Mike	Mike	Scott	11	2017	1	21721.77	10691.85	15843.00
Hospital West	212	Mike	Sam	Sally	Sally	Frank	1	2017	1	16012.43	9406.00	9406.00

Results wanted:

Entity	Physician	1-Cases	Chareges	Payments	Net Revenue
Hospital East	John	5	100611.91	28903.21	31566.00
Hospital West	John	3	52689.07	16584.85	27580.24

[v-ljerr-msft · Accepted solution]

Hi @tbobolz,

Based on my test, you should be able to follow steps below to get the expected result in your scenario.

1. I assume you have a table which has all the Physician names like below. Or you'll need to add one from your source side.

2.  Use the Physician column from the table above as Slicer.

3. Use the formula below to create a measure to get the selected Physician.

Selected Physician = SELECTEDVALUE(Table2[Physician],BLANK())

4. Use the formulas below to create four measures to recalculate 1-Cases, Charges, Payments, Net Revenue for the selected Physician.

1-Cases for Selected Physician = 
var selectedPhysician = SELECTEDVALUE(Table2[Physician],BLANK())
return
COUNTROWS(FILTER(Table1,Table1[Admitting Phy]=selectedPhysician||Table1[Attending Phy]=selectedPhysician||Table1[Referring Phy]=selectedPhysician||Table1[Primary Care Phy]=selectedPhysician||Table1[Surgeon]=selectedPhysician))

Charge for Selected Physician = 
var selectedPhysician = SELECTEDVALUE(Table2[Physician],BLANK())
return
SUMX(FILTER(Table1,Table1[Admitting Phy]=selectedPhysician||Table1[Attending Phy]=selectedPhysician||Table1[Referring Phy]=selectedPhysician||Table1[Primary Care Phy]=selectedPhysician||Table1[Surgeon]=selectedPhysician),[Charges])

Payments for Selected Physician = 
var selectedPhysician = SELECTEDVALUE(Table2[Physician],BLANK())
return
SUMX(FILTER(Table1,Table1[Admitting Phy]=selectedPhysician||Table1[Attending Phy]=selectedPhysician||Table1[Referring Phy]=selectedPhysician||Table1[Primary Care Phy]=selectedPhysician||Table1[Surgeon]=selectedPhysician),[Payments])

Net Revenue for Selected Physician = 
var selectedPhysician = SELECTEDVALUE(Table2[Physician],BLANK())
return
SUMX(FILTER(Table1,Table1[Admitting Phy]=selectedPhysician||Table1[Attending Phy]=selectedPhysician||Table1[Referring Phy]=selectedPhysician||Table1[Primary Care Phy]=selectedPhysician||Table1[Surgeon]=selectedPhysician),[Net Revenue])

5. Then show Entity column with all the five new created measures on your report.

Here is the sample pbix file for your reference. 

Regards

[tbobolz]

Thank you very much for the answer! Sorry for the late response, but have been stuck working something urgent and just now getting back to this.

Is it possible to create the "Physician" list from my current source data rather then load another data source into the model. I am working with an SQL Server Analysis Service Database and the option to add extrenal data is grayed-out. I am not sure if that is a condition of the SQL Server or my company has it set that way. I love your other appoarch; however, I am trying to aviod creating a report that will need manually updated each month.

Thanks again for your assistance and teaching

Terry

[tbobolz]

Thanks for you time! I can't wait to try this out later this week and let you know how it went.

Terry