cancel
Showing results for 
Search instead for 
Did you mean: 

L-M Excel to DAX Translation

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

 

LARGE function

LARGE Quick Measures Gallery

LCM function

LCM

LEFT, LEFTB functions

LEFT

LEN, LENB functions

LEN

LINEST function

 

LN function

LN

LOG function

LOG

LOG10 function

LOG10

LOGEST function

 

LOGINV function

EXP([mean]+[stdev]*NORM.S.INV([probability]))

LOGNORM.DIST function

PDF = 1/([x]*[stdev]*SQRT(2*PI()))* EXP(-1*POWER((LN([x])-[mean]),2)/(2*POWER([stdev],2)))

 

CDF = NORM.S.DIST((LN([x]) - [mean])/[stdev],TRUE)

LOGNORMDIST function

PDF = 1/([x]*[stdev]*SQRT(2*PI()))* EXP(-1*POWER((LN([x])-[mean]),2)/(2*POWER([stdev],2)))

 

CDF = NORM.S.DIST((LN([x]) - [mean])/[stdev],TRUE)

LOGNORM.INV function

EXP([mean]+[stdev]*NORM.S.INV([probability]))

LOOKUP function

LOOKUPVALUE

LOWER function

LOWER

MATCH function

N/A

MAX function

MAX or MAXX

MAXA function

MAXA

MAXIFS function

MAXX(FILTER(... && ...)...) or CALCULATE(MAX(),FILTER(... && ...))

MDETERM function

 

MDURATION function

Macaulay Duration Quick Measures Gallery

MEDIAN function

MEDIAN or MEDIANX

MID, MIDB functions

MID

MIN function

MIN or MINX

MINIFS function

MINX(FILTER(... && ...)...) or CALCULATE(MIN(),FILTER(... && ...))

MINA function

MINA

MINUTE function

MINUTE

MINVERSE function

 

MIRR function

MIRR = 
    VAR __Table = 'Table'
    VAR __FRate = [Finance_rate]
    VAR __RRate = [Reinvestment_rate]
    VAR __MIRRNum = 
        VAR __Max = MAXX(FILTER(__Table,[Value]>0),[Index])
        VAR __Table = 
            ADDCOLUMNS(
                FILTER(__Table,[Value]>0),
                "NPV",[Value] * POWER(1 + __RRate,__Max - [Index])
            )
    RETURN
        SUMX(__Table,[NPV])
    VAR __MIRRDenom = 
        VAR __Table = 
            ADDCOLUMNS(
                FILTER(__Table,[Value]<0),
                "NPV",[Value]/POWER((1+__FRate),[Index])
            )
    RETURN
        SUMX(__Table,[NPV]) * (1+__FRate)
RETURN
    POWER(-1 * __MIRRNum/__MIRRDenom,1/(COUNTROWS(__Table)-1)) - 1

 

MMULT function

MMULT Quick Measures Gallery

MOD function

MOD

MODE function

Mode Single Column Quick Measures Gallery

Mode Multi-Column Quick Measures Gallery

MODE.MULT function

Mode Single Column Quick Measures Gallery

Mode Multi-Column Quick Measures Gallery

MODE.SNGL function

Mode Single Column Quick Measures Gallery

Mode Multi-Column Quick Measures Gallery

MONTH function

MONTH

MROUND function

MROUND

MULTINOMIAL function

MULTINOMIAL Quick Measures Gallery

MUNIT function

MUNIT Quick Measures Gallery

 

Comments

Hi @Greg_Deckler 

thanks for such a great guide!

 

what do you know about MOD() function?

Check please, my issue https://community.powerbi.com/t5/Issues/MOD-DAX-function-strange-behaviour/idi-p/1081409#M56521

Maybe it is not an issue at all?

 

@az38 Well, what I can tell you is that the behavior you are seeing is exactly the behavior of Excel's MOD function. Wolfram Alpha returns 0 (see below). So, there is something wrong with Excel and DAX's MOD function with decimal numbers.

 

My understanding of modulus is that it is supposed to return the remainder. As you point out, there should be no remainder dividing 1.2 by .2.

 

image.png

Here is something interesting @az38 , in the documentation for the Excel MOD function:

https://support.office.com/en-us/article/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3

and DAX documentation on MOD:

https://docs.microsoft.com/en-us/dax/mod-function-dax

 

They provide the alternative formula for MOD:

The MOD function can be expressed in terms of the INT function: MOD(n, d) = n - d*INT(n/d)

 

If you use that formula, you get the correct results, 0 for even numbers like 1.2!!!

 

So....