Trying to create Calculated Column with values from another table.

[Spudder112]

Hi, I am trying to create Calculated Column with values from another table.

(This is a very simplified version of the tables, and it is probably too large to append, table 1 has more fields, but those are the relevant ones)

The column needs to be in Table 1.

Table 1

Employee ID

Employee Name

Table 2

Order Number

Order ID

Order Date

Order Amount

Employee ID

Customer ID

I want to add to table 1 a unique count of customers that each employee has made an order for. I want it to be a calculated column rather than measure so I can easily work with it later.

Example:

Employee ID   Employee Name    Customer Count

1212                Jon                         5

2455                Ron                        1

9559                Bon                        3

Although if I could make it a measure, and create these measures with it, then I would be open to using a measure instead.

With this I want to make:

Employee's servicing 2+ customers

% of Employee's servicing 2+ customers

So far I have made a measure Employee Customer Count = Distinctcountnoblank('Table2'[Customer ID]), and was able to pull it into a matrix as the value, with Table 1 Employee ID as the row and get an accurate customer count.

[amitchandak · Accepted solution]

@Spudder112 , Try like

calculate( Distinctcountnoblank('Table2'[Customer ID]),filter(Table2, Table1[Employee ID] = Table2[Employee ID]) )

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[amitchandak · Accepted solution]

@Spudder112 , Try like

calculate( Distinctcountnoblank('Table2'[Customer ID]),filter(Table2, Table1[Employee ID] = Table2[Employee ID]) )

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Spudder112 · Accepted solution]

Thank you! I had tried this formula as well. I realize now that it actually is working. 

My issue seems to actually stem from trying to trend the data by dates. With this new created column, what is the best way to show this ordered by dates. The Date field is in Table2 as well as a date table.

End result would be the ability to create the below. With that current formula, the customer count just repeats across dates.

Employee ID   Employee Name    1/1/2023    1/2/2023    1/3/2023

1212                Jon                         5                  2                3

2455                Ron                        1                  5                2

9559                Bon                        3                  7                1

I want to be able to say 66% of our employees serviced 2+ customers on 1/3/2023.