Forum Discussion
Returning a Value Using LASTNONBLANK
Hi,
I have a table that has SalesRep and associated Sales Manager at the time of an order in my FactSales table. My FactSales table is sorted from oldest to most recent order date and I'm trying to return a calculated column in the SalesRep Master table that pulls in their current Sales Manager. I've tried various calculations (one example below) but when there are multiple values for the Sales Manager to a rep it is not returning the newest value it seems to return the oldest instead of the newer Sales Manager. Can anyone help me out?
Thanks,
It might be sort of a flippant reply, but if LASTNONBLANK is getting you the oldest manager and you want the newest manager, would FIRSTNONBLANK work?
I would use TOPN and create this as a measure. You can change it to use OrderNum instead of OrderDate, if that's a better indicator of "current". Sample .pbix attached.
CurrentManager = CALCULATE( SELECTEDVALUE('Dim Sales Rep'[SalesManagerCode]), TOPN( 1, 'Dim Sales Rep', 'Dim Sales Rep'[Order Date], DESC))Hi chudson,
You also could try below measure to see whether it works or not
Measure 5 = CALCULATE(Min('Sales Rep'[SalesManagerCode]), FILTER(ALLEXCEPT('Sales Rep','Sales Rep'[SalesRepCode]),'Sales Rep'[Order Date]=MAX('Sales Rep'[Order Date])))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Can you upload some sample data and what you are looking for?
But a few quick notes:
1. Sort order of the table has no affect.
2. Everything happens in context, be it a filter context and/or row context
3. You never want to use FILTER on a fact table. Use filter on the smaller dimension tables
4. Do not add a calculated column to a fact table, that's a recipe for bad performance
Hi Anonymous ,
Below is some dummy data showing what I'm trying to accomplish. I'm trying to create the calculated column in the Dim Sales Rep Table not a fact table. I am referencing the fact table though.
As you can see I want to bring in the SalesManagerCode to the Dim Sales Rep table based on the most recent order date. When I use the calculated column above it returns the earlier manager code instead of the newest (use rep 101 for this example). Let me know if this helps explain the issue and what I'm trying to accomplish.
OrderNum Order Date SalesRepCode SalesManagerCode 1234 7/31/2019 101 201 1235 8/1/2019 102 401 1236 8/2/2019 102 401 1237 8/5/2019 103 501 1238 8/27/2019 101 301 1239 8/27/2019 101 301 SalesRepCode CurrentManager Result CurrentManagerCode Expected 101 201 301 102 401 401 103 501 501 Thanks,
Hi chudson,
You also could try below measure to see whether it works or not
Measure 5 = CALCULATE(Min('Sales Rep'[SalesManagerCode]), FILTER(ALLEXCEPT('Sales Rep','Sales Rep'[SalesRepCode]),'Sales Rep'[Order Date]=MAX('Sales Rep'[Order Date])))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It might be sort of a flippant reply, but if LASTNONBLANK is getting you the oldest manager and you want the newest manager, would FIRSTNONBLANK work?
I would use TOPN and create this as a measure. You can change it to use OrderNum instead of OrderDate, if that's a better indicator of "current". Sample .pbix attached.
CurrentManager = CALCULATE( SELECTEDVALUE('Dim Sales Rep'[SalesManagerCode]), TOPN( 1, 'Dim Sales Rep', 'Dim Sales Rep'[Order Date], DESC))
