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.
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,
Solved! Go to Solution.
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 Zhi
If 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))
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 Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |