Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |