Forum Discussion
DAX Query two tables - Need help
HI DiamonDave,
#1, It seems like in Sale tabel the 'name' field seems named 'Salesperson', perhaps you can use this to replace the searched table fields.
SalesContact = LOOKUPVALUE ( Employee[Contact], Employee[FullName], Sales[Salesperson], BLANK () )Comment:
Employee table means the table that you stored the employee information with contact. Sales Table is the table that you want to add the contact info that lookup from employee table.
#2, OK, it seems like the target is a static value and sales table already include date values.
You can take a look at the following measure formula: (I try to add a variable table to summary total field values based on current salesperson and date year, month group. Then get the average from variable table result and divide with target to get the percentage value)
Employee AVG = VAR target = 15000 VAR currEmployee = SELECTEDVALUE ( Sales[Salesperson] ) VAR startDate = CALCULATE ( MAX ( Employee[Date] ), FILTER ( ALLSELECTED ( Employee ), [FullName] = currEmployee ) ) VAR summary = SUMMARIZE ( ADDCOLUMNS ( FILTER ( ALLSELECTED ( Sales ), [Salesperson] = currEmployee && [Date] >= startDate ), "Year", YEAR ( Sales[Date] ), "Month", MONTH ( Sales[Date] ) ), [Salesperson], [Year], [Month], "monthlyTotal", SUM ( Sales[Total] ) ) RETURN DIVIDE ( AVERAGEX ( summary, [monthlyTotal] ), target, BLANK () )Regards,
Xiaoxin Sheng
Hi DiamonDave,
#1, I'd like to suggest you create a calculate column on sales table to use the lookupvalue function to get corresponding value based on current table field values.
Contact =
LOOKUPVALUE ( Employee[Contact], Employee[FullName], Sales[FullName], BLANK () )LOOKUPVALUE function (DAX) - DAX | Microsoft Learn
#2, I checked on your sample table records but not found any date fields existed in the sales table. Can you please share some more detail information or dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Thank you for answering the question #1, I have not been able to implement your suggestion, but that may be because I lack the proper understanding. As I said, I am a bit of a newbie.
ALL DATA IS SAMPLE ONLY AND DOES NOT REFLECT ACTUAL MATERIAL IN DATA SET
Deleted Users Table (called deleted)
| FullName | Date | Contact |
| Joe Bloggs | 11/MAY/2023 | Dee Frost |
So if I read your answer correctly, I would be creating a calculated column, which works thus:
SalesContact =
LOOKUPVALUE ( Employee[Contact], Employee[FullName], Deleted[FullName], BLANK () )
Although I have a relationship between the two tables, Employee and Deleted, I don't get anything appearing in the column. Perhaps I am doing something wrong.
In regards to #2, let's say for example that the data looks like the following:
SalesQuota (Now)
| Salesperson | JAN | FEB | MAR | QTR | EXP | TOTAL | AVG | % |
| Bloggs, Jo | 18000 | 12000 | 15000 | 4 | 45000 | 45000 | 15000 | 100.00 |
| Farmer, Giles | 17000 | 18000 | 4 | 45000 | 35000 | 17500 | 77.78 | |
| Frost, Dee | 17250 | 19025 | 10750 | 4 | 47025 | 45000 | 15675 | 104.50 |
SalesQuota (Should Be)
| Salesperson | JAN | FEB | MAR | QTR | EXP | TOTAL | AVG | % |
| Bloggs, Jo | 18000 | 12000 | 15000 | 4 | 45000 | 45000 | 15000 | 100.00 |
| Farmer, Giles | 17000 | 18000 | 4 | 30000 | 35000 | 17500 | 116.66 | |
| Frost, Dee | 17250 | 19025 | 10750 | 4 | 47025 | 45000 | 15675 | 104.50 |
So, as I said before each sales person has a total sales for each month, which is tabulated to represent like the (now) example above. The expectation calculates £15'000 x number of months in quarter (3) to get a prediction on sales target, However, if the member of staff missed a month, let's say Giles Farmer joined the company in February, he will not have any sales in Jan, but the table calculates as if he has recorded a zero.
Instead it needs to read the null value as just that, not a zero, so the calculation is 15000 x months with >0, which then adjusts the target accordingly to show as in the second version of the table.
I can't wait to overhaul this into something that works far better, but I am constrained for time and need to deal with this legacy in the short term.
If you could attach a sample database file to show how each of your suggestions (Q1 and Q2) works, that would be great. I can retrofit the alterations onto my data sets by mapping field names.
Many thanks for looking at this and offering suggestions.
HI DiamonDave,
#1, It seems like in Sale tabel the 'name' field seems named 'Salesperson', perhaps you can use this to replace the searched table fields.
SalesContact = LOOKUPVALUE ( Employee[Contact], Employee[FullName], Sales[Salesperson], BLANK () )Comment:
Employee table means the table that you stored the employee information with contact. Sales Table is the table that you want to add the contact info that lookup from employee table.
#2, OK, it seems like the target is a static value and sales table already include date values.
You can take a look at the following measure formula: (I try to add a variable table to summary total field values based on current salesperson and date year, month group. Then get the average from variable table result and divide with target to get the percentage value)
Employee AVG = VAR target = 15000 VAR currEmployee = SELECTEDVALUE ( Sales[Salesperson] ) VAR startDate = CALCULATE ( MAX ( Employee[Date] ), FILTER ( ALLSELECTED ( Employee ), [FullName] = currEmployee ) ) VAR summary = SUMMARIZE ( ADDCOLUMNS ( FILTER ( ALLSELECTED ( Sales ), [Salesperson] = currEmployee && [Date] >= startDate ), "Year", YEAR ( Sales[Date] ), "Month", MONTH ( Sales[Date] ) ), [Salesperson], [Year], [Month], "monthlyTotal", SUM ( Sales[Total] ) ) RETURN DIVIDE ( AVERAGEX ( summary, [monthlyTotal] ), target, BLANK () )Regards,
Xiaoxin Sheng