Forum Discussion
Helper IVReturning a Value Using LASTNONBLANK
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.
Helper IVHi 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,
Community SupportHi 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 Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.