Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
Hey,
sorry for the late response. Use calculated Table under --> Data-->modeling->New Table
LastReps = DISTINCT(Table1[Customer #])
Add Columns
LastDate = CALCULATE(MAX(Table1[Last order date]);FILTER(Table1;Table1[Customer #]=LastReps[Customer #]))
SalesRep = LOOKUPVALUE(Table1[Sales Rep];Table1[Customer #];LastReps[Customer #];Table1[Last order date];LastReps[LastDate])
There are other ways too, but I think this is the most comprohensive
Hey,
do you have a fact tables and dimensions?
Can you show us some example data?
thx
and here is a sample
Customer # | Sales Rep | Last order date |
1018574 | tim | 9/21/2017 |
1018574 | alex | 9/22/2017 |
1017640 | dirk | 1/12/2018 |
1017640 | pit | 8/23/2017 |
1017640 | john | 10/24/2017 |
1020601 | kris | 1/11/2018 |
1020601 | michael | 12/15/2017 |
1015972 | Lisa | 1/11/2018 |
1015972 | carsten | 1/15/2018 |
1015972 | maria | 9/4/2017 |
1015972 | tom | 12/28/2017 |
1015981 | carsten | 11/13/2017 |
1015981 | niels | 1/15/2018 |
1020722 | jenny | 12/1/2017 |
1020722 | peter | 1/4/2018 |
which i would like to trasfer to new table which will show me this
1018574 | alex |
1017640 | dirk |
1020601 | kris |
1015972 | carsten |
1015981 | niels |
1020722 | peter |
meaning dynamic sales rep/cst table based on last order date
Hope this makes it clear
Thanx
Hey,
sorry for the late response. Use calculated Table under --> Data-->modeling->New Table
LastReps = DISTINCT(Table1[Customer #])
Add Columns
LastDate = CALCULATE(MAX(Table1[Last order date]);FILTER(Table1;Table1[Customer #]=LastReps[Customer #]))
SalesRep = LOOKUPVALUE(Table1[Sales Rep];Table1[Customer #];LastReps[Customer #];Table1[Last order date];LastReps[LastDate])
There are other ways too, but I think this is the most comprohensive
Hello my friend i tried to use above SaleRep Dax but i got this error A table of multiple values was supplied where a single value was expected.
I have created new table for Customer ID and last order date now i need last sales representative for each customer.
My other table is as below
Customer # | Sales Representative Name | Entry Date |
3 | A | 17/04/2019 |
3 | B | 29/06/2021 |
1121 | AA | 14/02/2016 |
1121 | BB | 15/02/2016 |
1 | AAA | 5/10/2023 |
1 | BBB | 2/10/2023 |
1199 | AAAA | 16/10/2023 |
1199 | BBBB | 18/10/2023 |
Result should be
Customer # | Sales Representative Name | Entry Date |
3 | B | 29/06/2021 |
1121 | BB | 15/02/2016 |
1 | AAA | 5/10/2023 |
1199 | BBBB | 18/10/2023 |