Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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!
Proud to be a Super User!
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!
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |