Need to match users with account!

[RobertReeves]

Our company has recently moved to using Power BI for reporting. One of the reports is a trending report that is filtered for specific accoutns. The accounts the users handle are not stored in our system so it is not in the tables. I have created an excel table with the account / user / payor information. I have not been able to get this information to pull into BI correctly. I need to be to report all services provided for the particular account.

We can have 1 payor with mulitple accounts but the user is specific to the account. I've only been using BI for a few months so any assistance is greatly appreciated.

From the system table.

Primary User	Title1	Payor1	Secondary User	Title2	Payor2
User 1	TCM	Payor 1
User 1	TCM	Payor 1	User 2	ADJ	Payor 2
User 1	TCM	Payor 1	User 2	ADJ	Payor 2
User 1	TCM	Payor 1	User 2	ADJ	Payor 2
User 1	TCM	Payor 1	User 2	ADJ	Payor 2

Account Table.

User	Account	Payor
User 1	account 1	Payor 1
User 2	account 2	Payor 2
User 3	Account 3	Payor 2
User 4	Account 4	Payor 2
User 5	Account 4	Payor 2
User 6	Account 5	Payor 3
User 7	Account 6	Payor 2
User 8	Account 7	Payor 2
User 9	Account 8	Payor 4
User 10	Account 3	Payor 2
User 11	Account 9	Payor 3
User 12	Account 9	Payor 3
User 13	Account 4	Payor 2
User 14	Account 4	Payor 2

[v-yangliu-msft · Accepted solution]

Hi  @RobertReeves ,

Here are the steps you can follow：

1. Create calculated column.

Account =
MAXX(
    FILTER(ALL('Account Table'),
    'Account Table'[User]=EARLIER('From the system table'[Primary User])),'Account Table'[Account])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly