Forum Discussion

larsm11's avatar
larsm11
Frequent Visitor
6 years ago
Solved

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 t...
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    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!

JarroVGIT's avatar
JarroVGIT
Resident Rockstar
6 years ago

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's avatar
larsm11
Frequent Visitor
6 years ago

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