Forum Discussion
Create Custom Column based on filter in Related table
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
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!
2 Replies
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!
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
