Forum Discussion
Get max value from related table
Hi there.
I have got 2 tables linked. One with items, like:
Item_ID - Description....
The other one is about pricing of items. This is:
Item_ID - Year - Month - Sales_Amount
There's a relationship between both Item_ID fields.
In the first one, I created a measure to get last year where a item was sold.
This is:
lastYear := MAX(Sales[Year])
Then, a second measure to get the last sales month. This is:
lastMonth := CALCULATE(MAX('Sales'[Month]);FILTER(Sales;Sales[Year]=MAX(Sales[Year])))
Finally, a third measure with the amount of sold item. (I wanna know what was the last item price, due I haven't got any date, just year and month as integers)
This measure is:
Amount:=CALCULATE(
MAX(Sales[Amount]);
FILTER(Sales;[Year]=[lastYear]);
FILTER(Sales;[Month]=[lastMont])
)
It doesn't work and I get only list price. Changing FILTER(Sales;[Year] = MAX(Sales[Year])) instead, I get some results but not the last price.
Cheers.
First of all, your statements are not clear. You say you want the amount on the latest month of the latest year.... and then you say something about list price. Where the heck do you have a list price column in your tables?
When using FILTER, you should always remember that if you use a measure in the filtering condition, the measure performs a context transition because each measure is wrapped in CALCULATE implicitly. You should understand how context transition works before you even start using (measures in) FILTERs.
Here's probably what you want:
[Latest Sale Year] = MAX( Sales[Year] ) [Latest Sale Month] = var __latestYear = [Latest Year] var __latestMonth = CALCULATE( MAX( Sales[Month] ), Sales[Year] = __latestYear ) return __latestMonth [Latest Amount] = var __latestMonth = [Latest Month] var __latestYear = [Latest Year] var __latestAmount = CALCULATE( MAX( Sales[Amount] ), Sales[Year] = __latestYear, Sales[Month] = __latestMonth ) return __latestAmount
If you have selected many items, you'll get the maximum amount for any of the items in the set that have the same latest year and latest month. If you want this measure(s) to work only when one item is visible, then you must guard your measures with HASONEVALUE or HASONEFILTER.
Best
Darek
3 Replies
First of all, your statements are not clear. You say you want the amount on the latest month of the latest year.... and then you say something about list price. Where the heck do you have a list price column in your tables?
When using FILTER, you should always remember that if you use a measure in the filtering condition, the measure performs a context transition because each measure is wrapped in CALCULATE implicitly. You should understand how context transition works before you even start using (measures in) FILTERs.
Here's probably what you want:
[Latest Sale Year] = MAX( Sales[Year] ) [Latest Sale Month] = var __latestYear = [Latest Year] var __latestMonth = CALCULATE( MAX( Sales[Month] ), Sales[Year] = __latestYear ) return __latestMonth [Latest Amount] = var __latestMonth = [Latest Month] var __latestYear = [Latest Year] var __latestAmount = CALCULATE( MAX( Sales[Amount] ), Sales[Year] = __latestYear, Sales[Month] = __latestMonth ) return __latestAmount
If you have selected many items, you'll get the maximum amount for any of the items in the set that have the same latest year and latest month. If you want this measure(s) to work only when one item is visible, then you must guard your measures with HASONEVALUE or HASONEFILTER.
Best
Darek
By the way... Your current design is not proper. You should change it as soon as possible before your model becomes a nightmare. Please try to follow Best Practices of model design for tabular cubes. If you don't, then your DAX will become more and more complex to the point where you'll not know what it calculates. Just a piece of good advice...
Best
Darek