Create Custom Column based on filter in Related table

[larsm11]

Quite new to DAX, and have gotten myself into a dead end.

I've have two tables, where Table2 holds transactions for Table1 and they share id. The id is unique in Table 1 (i.e. no duplicates).

I'm trying to (i.e. need help to ) create a DAX expression to create a custom column in Table 1 based on the following:

• Find all rows in Table 2 with the same id as the id in Table 1
• Identify the one row with the highest positive amount and the one with highest negative amount, and determine the days between them using the date column (see sample tables below)
• Rows with smaller amounts in between the high and low amounts are not or important

Table 1			Table 2
id	days<calculated>		id	date	amount
2	10		2	2019-01-15	3500
			2	2019-01-25	-3500
			2	2019-03-15	-200

Many thanks in advance,

/LarsM

[JarroVGIT · Accepted solution]

Hi @larsm11 ,

The following DAX will create the column you are looking for. Just a warning: a calculated column is not evaluated when you apply new filters, only when you do a data refresh the calculated column is re-evaluated. If you want it to be dynamic, you will need to use measures.

daysBetween = 
VAR _curID = Table1[ID]
VAR _highestAmount = MAXX(FILTER(Table2, Table2[id] = _curID), [amount])
VAR _lowestAmount = MINX(FILTER(Table2, Table2[id] = _curID), [amount])
VAR _highestDate = MAXX(FILTER(Table2, Table2[id] = _curID && Table2[amount] = _highestAmount), [date])
VAR _lowestDate = MAXX(FILTER(Table2, Table2[id] = _curID && Table2[amount] = _lowestAmount), [date])
RETURN
DATEDIFF(_highestDate, _lowestDate, DAY)

I used variables as much as possible to illustrate the logic 🙂 Let me know if this solves your issue!

[larsm11]

Excellent,

Thanks for bringing me a great and working solution in such a short timeframe, and for using variables to make it so readable for a newbie like myself.

/LarsM