Forum Discussion

tecumseh's avatar
tecumseh
Resolver III
2 years ago
Solved

Lookup A Value from Another Table All In One DAX With Many VAR

Hi, I have several VAR's in one DAX Statement. I checked each one as I created so they are all fine. I'm trying to add a new step to add a column from one table to another table, but th tables d...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi tecumseh ,

     

    There is no need to split into multiple calculation tables, just in the same calculation table. Using Lookup is table based, your VAR uses multiple ADDCOLUMNS but doesn't retrun it at the end, implying that it wasn't actually created, just an intermediate process. So you just split it into two DAX expressions.

    Table for two var = var _t1 = ADDCOLUMNS('Table',"For bigger Sum",SUMX(FILTER(ALL('Table'),EARLIER([ID])<[ID]),[Value]))
var _t2 = ADDCOLUMNS(_t1,"For smaller Sum",SUMX(FILTER(ALL('Table'),EARLIER([ID])>[ID]),[Value]))
RETURN _t2

ID For 3 = LOOKUPVALUE('Table for two var'[For bigger Sum],[ID],3)

ID For 4 = LOOKUPVALUE('Table for two var'[For bigger Sum],[ID],4)

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tecumseh ,

 

There is no need to split into multiple calculation tables, just in the same calculation table. Using Lookup is table based, your VAR uses multiple ADDCOLUMNS but doesn't retrun it at the end, implying that it wasn't actually created, just an intermediate process. So you just split it into two DAX expressions.

Table for two var = var _t1 = ADDCOLUMNS('Table',"For bigger Sum",SUMX(FILTER(ALL('Table'),EARLIER([ID])<[ID]),[Value]))
var _t2 = ADDCOLUMNS(_t1,"For smaller Sum",SUMX(FILTER(ALL('Table'),EARLIER([ID])>[ID]),[Value]))
RETURN _t2

ID For 3 = LOOKUPVALUE('Table for two var'[For bigger Sum],[ID],3)

ID For 4 = LOOKUPVALUE('Table for two var'[For bigger Sum],[ID],4)

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.