Calculated Column - Return the value from a second table with the most dates

[jrjeffcoat64]

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

[Phil_Seamark]

Hi @jrjeffcoat64

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

[jrjeffcoat64]

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

[Phil_Seamark]

Hi @jrjeffcoat64

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])

[jrjeffcoat64]

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

[Phil_Seamark · Accepted 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])

[Zubair_Muhammad]

@jrjeffcoat64

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 )