Forum Discussion
Select multiple items from different slicer - without limiting other columns
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.
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. :smileyhappy:
Regards
5 Replies
Can you post your sample data? Seems like you really should unpivot those 5 fields.
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 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. :smileyhappy:
Regards
