cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chudson Regular Visitor
Regular Visitor

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?  

 

 
 CALCULATE(LASTNONBLANK(FactSales[RBM],MAX(FactSales[RevenueRecognitionDate])),FILTER(ALL(FactSales),FactSales[RepID]=DimSurgeon[RepID]))

 

 

 

Thanks,

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Returning 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))

 

View solution in original post

Community Support Team
Community Support Team

Re: Returning a Value Using LASTNONBLANK

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])))

287.PNG

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.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Returning a Value Using LASTNONBLANK

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

chudson Regular Visitor
Regular Visitor

Re: Returning a Value Using LASTNONBLANK

Hi @Nick_M ,

 

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.

 

OrderNumOrder DateSalesRepCodeSalesManagerCode
12347/31/2019101201
12358/1/2019102401
12368/2/2019102401
12378/5/2019103501
12388/27/2019101301
12398/27/2019101301

 

 

SalesRepCodeCurrentManager ResultCurrentManagerCode Expected
101201301
102401401
103501501

 

Thanks,

Super User
Super User

Re: Returning 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))

 

View solution in original post

Community Support Team
Community Support Team

Re: Returning a Value Using LASTNONBLANK

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])))

287.PNG

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.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)