Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've been been struggling with solving my lookup issue for weeks. Now its time to ask the experts. Hopefully I can explain it well enough to get some help.
I have two tables
1. Patients
Field: Patient_ID
Field: PCP_ATTRB (This will be the calculated field)
Values in the Table
Patient_ID | PCP_ATTRB |
|
123 |
|
|
456 |
|
|
789 |
|
|
|
|
|
|
|
|
|
|
|
2. Claims
Field: Patient_ID
Field: Provider_ID
Field: Service_Date
Values in the Table
Patient_ID
Patient_ID | Provider_ID | Service_Date |
123 | 888 | 1/4/2018 |
123 | 999 | 1/5/2018 |
123 | 888 | 1/4/2018 |
456 | 777 | 1/11/18 |
456 | 555 | 1/1/18 |
789 | 555 | 1/12/18 |
123 | 999 | 1/6/2018 |
123 | 999 | 1/6/2018 |
123 | 888 | 1/10/2018 |
123 | 888 | 1/11/2018 |
123 | 888 | 1/15/2018 |
For each Patient_ID in table 1, I need the code to lookup all the rows in table 2 that have the same Patient_ID value, then find the Provider_ID with the most Service_Dates, then return that Provider_ID back to table 1 in the PCP_ATTRB field.
So the code would look for Patient_ID "123" in table and find 8 matching rows. For Provider_ID "999" it would find 3 rows but only 2 unique Service_Date values. For Provider_ID "888" it would find 5 rows, but only 4 unique Service_Date values. Since Provider_ID "888" has more unique Service_Date values, return the Provider_ID "888" back to table 1 calculated PCP_ATTRB.
In the event that two Provider_ID values in table 2 have the same number of unique Service_Date values, return the Provider_ID with the most recent Service_Date. Patient_ID would bring back Provider_ID "777" since it has the most recent Service Date of 1/11/18.
Table 1 should look like this after the lookup. Thank you in advance for any help or suggestions in resolving my problem
Patient_ID | PCP_ATTRB |
|
123 | 888 |
|
456 | 777 |
|
789 | 555 |
|
|
|
|
|
|
|
|
|
|
Solved! Go to Solution.
Oh yeah, good point. I changed the function, but forgot to add the parameter. I have highlighed in bold the change
Please try this
Column = VAR myPatientID = 'Patients'[Patient_ID] VAR FirstSummaryTable = GROUPBY( FILTER( 'Claims', 'Claims'[Patient_ID]=myPatientID ), 'Claims'[Patient_ID], 'Claims'[Provider_ID], "Count of Rows per Provider",COUNTAX(CURRENTGROUP(),'Claims'[Service_Date])) VAR SecondSummaryTable = TOPN(1,FirstSummaryTable,[Count of Rows per Provider],DESC) RETURN MAXX(SecondSummaryTable,[Provider_ID])
I found this calculated column on 'Claims' worked with your sample data, but may need more tweaking on ties (with a bigger data set)
Column = VAR myPatientID = 'Patients'[Patient_ID] VAR FirstSummaryTable = GROUPBY( FILTER( 'Claims','Claims'[Patient_ID]=myPatientID), 'Claims'[Patient_ID], 'Claims'[Provider_ID], "Count of Rows per Provider",COUNTX(CURRENTGROUP(),1)) VAR SecondSummaryTable = TOPN(1,FirstSummaryTable,[Count of Rows per Provider],DESC) RETURN MAXX(SecondSummaryTable,[Provider_ID])
Here is a link to the PBIX file
https://1drv.ms/u/s!AtDlC2rep7a-oXkC-quZxJSHfVa6
Phil,
Thanks for your help. I think you almost solved my problem, but it needs a little adjustment.
Your code returns the Provider with the most rows, I need the Provider with the most unique service dates. Basically, If I go see Provider "999" on 5 different days and see Provider "888" on 3 different day, I need to return Provider "999" back to the calculated column.
There might need to be one more grouping... but I just can't figure it out. What do you think???
Thanks again for responding.... your help is greatly appreciated
Try this slight adjustment for the unique rows. It uses COUNTAX in place of COUNTX
Column = VAR myPatientID = 'Patients'[Patient_ID] VAR FirstSummaryTable = GROUPBY( FILTER( 'Claims','Claims'[Patient_ID]=myPatientID), 'Claims'[Patient_ID], 'Claims'[Provider_ID], "Count of Rows per Provider",COUNTAX(CURRENTGROUP(),1)) VAR SecondSummaryTable = TOPN(1,FirstSummaryTable,[Count of Rows per Provider],DESC) RETURN MAXX(SecondSummaryTable,[Provider_ID])
Phil,
Where in your code do you determine which provider has the most unique service dates? The Provider I have seen on the most different days is what I need returned.
Thanks
Jim
Oh yeah, good point. I changed the function, but forgot to add the parameter. I have highlighed in bold the change
Please try this
Column = VAR myPatientID = 'Patients'[Patient_ID] VAR FirstSummaryTable = GROUPBY( FILTER( 'Claims', 'Claims'[Patient_ID]=myPatientID ), 'Claims'[Patient_ID], 'Claims'[Provider_ID], "Count of Rows per Provider",COUNTAX(CURRENTGROUP(),'Claims'[Service_Date])) VAR SecondSummaryTable = TOPN(1,FirstSummaryTable,[Count of Rows per Provider],DESC) RETURN MAXX(SecondSummaryTable,[Provider_ID])
Just an alternate way of doing this.
Hopefully it will work.
Assuming 2 tables are related on Patient_ID Column
PCP_ATTRB = VAR mytable = CALCULATETABLE ( TOPN ( 1, SUMMARIZE ( Claims, Claims[Provider_ID], "Count Unique", DISTINCTCOUNT ( Claims[Service_Date] ), "Last Date", MAX ( Claims[Service_Date] ) ), [Count Unique] ) ) VAR MostDates = CALCULATE ( LASTNONBLANK ( Claims[Provider_ID], 1 ), mytable ) VAR MaxDate = CALCULATE ( LASTNONBLANK ( Claims[Provider_ID], 1 ), FILTER ( mytable, [Last Date] = MAXX ( mytable, [Last Date] ) ) ) RETURN IF ( CALCULATE ( COUNTROWS ( mytable ) ) = 1, MostDates, MaxDate )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |