Visual to include values from 2 databases

[knight4life]

I am trying to create a report function to analyze our phone system call logs. I have most of it already configured with 1 Table visual that includes User Number, placed calls, missed calls, received calls, and avg call duration. I also have a couple other Card visual's that just highlights the same information when a user number is clicked. 

The problem I have is that my data that i am able to collect from my phone system only included the user number, and not the username. 

I have created a second data source, EmployeeDB, in power bi that has 2 columns, Col1 is the usernumber and Col2 is the username. 

What I want to see if is possible, is in my Table Visual add another value that will look for the usernumber in my EmployeeDB, find that username and add it to the visual corresponding to each line.

Hopefully I explained that well enough, but let me know if you have any questions.

Thank you,

[lbendlin · Accepted solution]

Yes, this is a standard feature. Use the data model view to link the tables together. Then you can add the needed columns to the visual.

[lbendlin]

This generally depends on the visual you are using, but keep in mind that measures are computed for each cell of the visual, including the data cells, the row and column totals, and the grand total.  That allows you to influence what to show where (or not to show, by returning BLANK() ).

[knight4life]

@lbendlin Ok, so I finally got the visual to show the data properly by changing the username field on the visual to show First. Hopefully my last question is at the bottom it totals up all the columns information and displays the results which is great, but is there a way to exclude that username column from that total row?

Thanks,

[lbendlin · Accepted solution]

your "employeedb"  table will act as the dimension table because each user appears there only once, and your "callogdb"  table will act as the fact table as you can have from zero to many calls per user.

The relationship will be 1:* from employeedb to callogdb, linked by the usernumber.

[knight4life]

Ok, I have that now. Should I just be able to drag the field of Username from EmployeesDB onto the visual that already had the fields from the calllogsdb? When I do that, it just repeats the username's from employeeDB over and over again instead of matching the usernames to the correct instance on the visual. 

Thank you,

[lbendlin · Accepted solution]

Yes, this is a standard feature. Use the data model view to link the tables together. Then you can add the needed columns to the visual.

[knight4life]

 if I want to pull the username from EmployeeDB to display on the corresponding row of my visual do I create a many to one relationship between the User Number? Here is an example of my data:

EmployeeDB

UserNumber: 01

UserName: user 1

CallLogDB

UserNumber: 1

CallDuration: 05

CallDirection: Placed

Visual will show UserNumber, CallDuartion, and CallDirection, but I also need it to show the corresponding username.