Forum Discussion
Return a corresponding value from another table based on calculated measure
Hi!
I have been struggling with this kind of task for days now. I made a calculated measure that will return an age of a machine in the given time (see a picture below).
The age of the machine defines a buyback rate that I would like to find from another table to calculate the buyback price for the machine. I have a table that has two columns Age (months) and Buyback rate %, creating a calculated column is not an option since I want this to work dynamically. All the solutions that I have tried so far have failed and I only get blank values as a result. Any help on that one?
Anonymous
Not enough information but can you try the following meaure:RateMeasure = VAR __AgeSelected = MAX ( ThisTable[MachineAge] ) RETURN CALCULATE ( MAX ( AgeTablename[Buyback rate %] ), AgeTablename[Age] = __AgeSelected )Replace the table and columns names as per your model
Hey! The measure that you posted was unfortunately not working directly (blame is on me, I didn't provide enough information). Luckily I got the measure working, adding the rounddown function on the age and deleting the Max statement from "var age" function was the key as shown below.
Machine age := var age = (MAX(vDimCalendar[Date]) - MAX(Buybacks[Delivered (month/year)])) / 365 * 12 return ROUNDDOWN(age, 0) --------------------------------------------------------------------------------------------- Buy-back policy rate := VAR __AgeSelected = 'Measures table'[Machine age] RETURN CALCULATE ( MAX ( 'Buyback policy rates'[Trade-in] ), 'Buyback policy rates'[Month] = __AgeSelected )
2 Replies
- Fowmy
Super User
Anonymous
Not enough information but can you try the following meaure:RateMeasure = VAR __AgeSelected = MAX ( ThisTable[MachineAge] ) RETURN CALCULATE ( MAX ( AgeTablename[Buyback rate %] ), AgeTablename[Age] = __AgeSelected )Replace the table and columns names as per your model
Hey! The measure that you posted was unfortunately not working directly (blame is on me, I didn't provide enough information). Luckily I got the measure working, adding the rounddown function on the age and deleting the Max statement from "var age" function was the key as shown below.
Machine age := var age = (MAX(vDimCalendar[Date]) - MAX(Buybacks[Delivered (month/year)])) / 365 * 12 return ROUNDDOWN(age, 0) --------------------------------------------------------------------------------------------- Buy-back policy rate := VAR __AgeSelected = 'Measures table'[Machine age] RETURN CALCULATE ( MAX ( 'Buyback policy rates'[Trade-in] ), 'Buyback policy rates'[Month] = __AgeSelected )