Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

Thank you so much, Darek. It works perfectly.
I'm noob in DAX.

 

Best regards.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors