Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to 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
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.
@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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
93 | |
93 | |
84 | |
81 | |
49 |
User | Count |
---|---|
145 | |
142 | |
111 | |
71 | |
55 |