cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Trying to create Calculated Column with values from another table.

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.

2 ACCEPTED SOLUTIONS
Super User

@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

Helper I

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.

2 REPLIES 2
Super User

@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

Helper I

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors