Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Excel equivalent functions for DAX

Hi All, I am looking for the Excel equivalent functions for DAX, can someone let me know how we can Implement the below functions in DAX.   Numbervalue Sumifs Vlookup   Regards,
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Anonymous

     

    NUMBERVALUE:

    To convert text to number, you can use VALUE() function.

     

    SUMIFS:

    You can use CALCULATE() and apply filter context. Please refer to this blog: PowerPivot DAX: CALCULATE is a supercharged SUMIF

     

    =CALCULATE(SUM(Table[Amount]),FILTER(Table,Table[Column1] = "A" && Table[Column2] = "B"))

     

    VLOOKUP:

    In DAX, it can't evaluate at cell level like VLOOKUP since it can only evaluate on row level. You can use RELATED(), RELATEDTABLE() or LOOKUPVALUE() to achieve similar logic. For more details, please refer to blogs below:

     

    VLOOKUP Functionality in Power Pivot

    Equivalent of VLOOKUP in DAX

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

Anonymous

 

NUMBERVALUE:

To convert text to number, you can use VALUE() function.

 

SUMIFS:

You can use CALCULATE() and apply filter context. Please refer to this blog: PowerPivot DAX: CALCULATE is a supercharged SUMIF

 

=CALCULATE(SUM(Table[Amount]),FILTER(Table,Table[Column1] = "A" && Table[Column2] = "B"))

 

VLOOKUP:

In DAX, it can't evaluate at cell level like VLOOKUP since it can only evaluate on row level. You can use RELATED(), RELATEDTABLE() or LOOKUPVALUE() to achieve similar logic. For more details, please refer to blogs below:

 

VLOOKUP Functionality in Power Pivot

Equivalent of VLOOKUP in DAX

 

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks a lot Simon_Hou-MSFT  :)