Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Spudder112
Helper I
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
amitchandak
Super User
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
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

View solution in original post

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.

View solution in original post

2 REPLIES 2
amitchandak
Super User
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
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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