cancel
Showing results for 
Search instead for 
Did you mean: 

S Excel to DAX Translation

This is part of a series of posts on Excel to DAX translation. The main article is here.

 

SEARCH, SEARCHB functions

SEARCH

SEC function

DIVIDE(1,SIN(...),0)

SECH function

DIVIDE(1,SINH(...),0)

SECOND function

SECOND

SEQUENCE function

GENERATESERIES

SERIESSUM function

SERIESSUM Quick Measures Gallery

SHEET function

N/A

SHEETS function

N/A

SIGN function

SIGN

SIN function

SIN

SINH function

SINH

SKEW function

Pearson's Coefficient of Skewness Quick Measures Gallery

SKEW.P function

Pearson's Coefficient of Skewness Quick Measures Gallery

SLN function

SLN or ([Cost] - [Salvage]) / [Life]

SLOPE function

SLOPE = 
    VAR __XBar = AVERAGEX('Table',[x])
    VAR __YBar = AVERAGEX('Table',[y])
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "Numerator",([x] - __XBar) * ([y] - __YBar),
            "Denominator",POWER([x] - __XBar,2)
        )
RETURN
    DIVIDE(
        SUMX(__Table,[Numerator]),
        SUMX(__Table,[Denominator])
    )

SMALL function

SMALL Quick Measures Gallery

SORT function

N/A

SORTBY function

N/A

SQRT function

SQRT

SQRTPI function

SQRTPI

STANDARDIZE function

( ... - AVERAGE(...) ) / STDEV.S(...)

A number, minus a mean divided by the standard deviation. STANDARDIZE isn't even as smart as the formula provided, you have to give it the mean and standard deviation.

STDEV function

STDEV.S or STDEVX.S

STDEV.P function

STDEV.P or STDEVX.P

STDEV.S function

STDEV.S or STDEVX.S

STDEVA function

STDEV.S or STDEVX.S

STDEVP function

STDEV.P or STDEVX.P

STDEVPA function

STDEV.P or STDEVX.P

STEYX function

STEYX = 
    VAR __XBar = AVERAGEX('Table',[x])
    VAR __YBar = AVERAGEX('Table',[y])
    VAR __Table = 
        ADDCOLUMNS(
            'Table',
            "Numerator",([x] - __XBar) * ([y] - __YBar),
            "Denominator",POWER([x] - __XBar,2),
            "YPart",POWER([y] - __YBar,2)
        )
    VAR __N = COUNTROWS(__Table)
RETURN
    SQRT(
        1 / (__N - 2)
        *
        (
            SUMX(__Table,[YPart])
            -
            DIVIDE(
                POWER(SUMX(__Table,[Numerator]),2),
                SUMX(__Table,[Denominator])
            )
        )
    )

SUBSTITUTE function

SUBSTITUTE

SUBTOTAL function

N/A

SUM function

SUM

SUMIF function

SUMX(FILTER(...)...) or CALCALUTE(SUM(...),FILTER(...))

SUMIFS function

SUMX(FILTER(... && ...)...) or CALCALUTE(SUM(...),FILTER(... && ...))

SUMPRODUCT function

SUMPRODUCT = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",[Value1] * RELATED(Table2[Value2])
        )
RETURN
    SUMX(__Table,[Value])

SUMSQ function

SUMX(...,POWER([...],2))

SUMX2MY2 function

SUMX2MY2 = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",POWER([Value1],2) - POWER(RELATED(Table2[Value2]),2)
        )
RETURN
    SUMX(__Table,[Value])

SUMX2PY2 function

SUMX2PY2 = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",POWER([Value1],2) + POWER(RELATED(Table2[Value2]),2)
        )
RETURN
    SUMX(__Table,[Value])

SUMXMY2 function

SUMXMY2 = 
    VAR __Table = 
        ADDCOLUMNS(
            'Table1',
            "Value",POWER([Value1] - RELATED(Table2[Value2]),2)
        )
RETURN
    SUMX(__Table,[Value])

SWITCH function

SWITCH

SYD function

SYD or

DIVIDE(
  ([Cost] - [Salvage]) * ([Life] - [Per] + 1) * 2,
  [Life] * ([Life] + 1)
)