Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I am trying to get a table wth cst and sales rep based on the last order date
so I have a table
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 |
and I would like to get a new table which will look like this
1018574 | alex |
1017640 | dirk |
1020601 | kris |
1015972 | carsten |
1015981 | niels |
1020722 | peter |
thank you
Solved! Go to Solution.
@Anonymous
As a calculated column you could use
Column = VAR MyMax = CALCULATE ( MAX ( TableName[Last order date] ), ALLEXCEPT ( TableName, TableName[Customer #] ) ) RETURN CALCULATE ( FIRSTNONBLANK ( TableName[Sales Rep], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Customer #] ), TableName[Last order date] = MyMax ) )
@Anonymous
It works when I use the sample data you have provided. See the pic below
Could you show me a screenshot of your formula?
or share the file via onedrive or googledrive
@Anonymous just create a dax measure do a LastDate = lastdate(datefield) and use the LastDate
Proud to be a Super User!
Thanx @vanessafvg
That doesnt work because i have multiple sales reps per customer.
Example customer
1015970 was maintained by sales rep maria in sept 2017, by tom in december 2017, by lisa on the 11th of Jan and finaly by Tom on 15th of Jan 2018
I would like to see only TOM
Thats why i am trying to get a column where i will see the last date per customer and then use it in another column where i would liek to use if function or something like that..
would be great if you can help me somehow becasue i am really strugeling here..
thanx for your time
@Anonymous ok you need to summarzie the table in my opinion
summarizetable = SUMMARIZE(data,Data[Customer #], Data[Sales Rep], "Lastdate", max(Data[Last order date]) )
from the new table you should be abel to pull the last per record
Proud to be a Super User!
@Anonymous
Try this MEASURE.
Then Drag Customer # and this MEASURE to a TABLE visual
LastSalesRep = CALCULATE ( SELECTEDVALUE ( TableName[Sales Rep] ), FILTER ( TableName, TableName[Last order date] = MAX ( TableName[Last order date] ) ) )
This looks promissing however i get this error for some reason
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
what can be this linked to
Hi @Anonymous
Try this revision
LastSalesRep = CALCULATE ( FIRSTNONBLANK ( TableName[Sales Rep], 1 ), FILTER ( TableName, TableName[Last order date] = MAX ( TableName[Last order date] ) ) )
@Anonymous
Error might be due to the fact that there could be 2 sales rep on the same latest date
@Anonymous
Alternatively you could use this formula as well
As a MEASURE
LastSalesRep_ = CALCULATE ( CONCATENATEX ( FILTER ( TableName, TableName[Last order date] = MAX ( TableName[Last order date] ) ), TableName[Sales Rep], ", " ) )
@Anonymous
As a calculated column you could use
Column = VAR MyMax = CALCULATE ( MAX ( TableName[Last order date] ), ALLEXCEPT ( TableName, TableName[Customer #] ) ) RETURN CALCULATE ( FIRSTNONBLANK ( TableName[Sales Rep], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Customer #] ), TableName[Last order date] = MyMax ) )
THe measures work perfectly but the column actually doesnt, It will always show only one person and thats i guess the sales person that placed the order last and is first in the alpabetical order.
Only if I use the CONCATENATEX version i get the right result but then there are the other results behind it in case of same order date..
How can we modify the calculate column formlua that way that it will get the last sales rep per customer/account .. so what the measure does but as a column
Thank you
@Anonymous
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |