Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RobertReeves
Frequent Visitor

Need to match users with account!

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 UserTitle1Payor1Secondary UserTitle2Payor2
User 1TCMPayor 1   
User 1TCMPayor 1User 2ADJPayor 2
User 1TCMPayor 1User 2ADJPayor 2
User 1TCMPayor 1User 2ADJPayor 2
User 1TCMPayor 1User 2ADJPayor 2

 

Account Table.

UserAccountPayor
User 1account 1Payor 1
User 2account 2Payor 2
User 3Account 3Payor 2
User 4Account 4Payor 2
User 5Account 4Payor 2
User 6Account 5Payor 3
User 7Account 6Payor 2
User 8Account 7Payor 2
User 9Account 8Payor 4
User 10Account 3Payor 2
User 11Account 9Payor 3
User 12Account 9Payor 3
User 13Account 4Payor 2
User 14Account 4Payor 2
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1675301800891.png

 

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

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1675301800891.png

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.