IF DAX Function (Logical)

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

Syntax

IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )
Parameter Attributes Description
LogicalTest

Any value or expression that can be evaluated to TRUE or FALSE.

ResultIfTrue

The value that is returned if the logical test is TRUE.

ResultIfFalse Optional

The value that is returned if the logical test is FALSE.
If omitted, a BLANK is returned.

Return values

Scalar A single value of any type.

Either ResultIfTrue or ResultIfFalse expression result, depending on LogicalTest.

Remarks

Using IF can generate multiple branches of code execution that could result in slower performance at query time.
Then IF can return BLANK as one of the results, there are cases where using DIVIDE to obtain the same result could produce a faster query plan.
The following code returns BLANK if LogicalTest is false.

IF ( <LogicalTest>, <ResultIfTrue> )

The previous code is semantically equivalent to the following expression:

DIVIDE ( <ResultIfTrue>, <LogicalTest> )
» 6 related articles
» 3 related functions

Examples

--  IF evaluates one of two branches, depending on the condition
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Product'[Brand] ),
    "Sales 1", [Sales Amount],
    "Sales 2",
        VAR SalesAmount = [Sales Amount]
        RETURN
            IF ( SalesAmount > 3000000, 3000000, SalesAmount ),
    "Sales 3", MIN ( [Sales Amount], 3000000 )
)
ORDER BY 'Product'[Brand]
Brand Sales 1 Sales 2 Sales 3
A. Datum 2,096,184.64 2,096,184.64 2,096,184.64
Adventure Works 4,011,112.28 3,000,000.00 3,000,000.00
Contoso 7,352,399.03 3,000,000.00 3,000,000.00
Fabrikam 5,554,015.73 3,000,000.00 3,000,000.00
Litware 3,255,704.03 3,000,000.00 3,000,000.00
Northwind Traders 1,040,552.13 1,040,552.13 1,040,552.13
Proseware 2,546,144.16 2,546,144.16 2,546,144.16
Southridge Video 1,384,413.85 1,384,413.85 1,384,413.85
Tailspin Toys 325,042.42 325,042.42 325,042.42
The Phone Company 1,123,819.07 1,123,819.07 1,123,819.07
Wide World Importers 1,901,956.66 1,901,956.66 1,901,956.66
--  The "else" branch is optional. 
--  If not specified, it defaults to BLANK
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Product'[Brand] ),
    "Sales 1", [Sales Amount],
    "Sales 2",
        VAR SalesAmount = [Sales Amount]
        RETURN
            IF ( SalesAmount > 3000000, 3000000 )
)
ORDER BY 'Product'[Brand]
Brand Sales 1 Sales 2
A. Datum 2,096,184.64 (Blank)
Adventure Works 4,011,112.28 3,000,000
Contoso 7,352,399.03 3,000,000
Fabrikam 5,554,015.73 3,000,000
Litware 3,255,704.03 3,000,000
Northwind Traders 1,040,552.13 (Blank)
Proseware 2,546,144.16 (Blank)
Southridge Video 1,384,413.85 (Blank)
Tailspin Toys 325,042.42 (Blank)
The Phone Company 1,123,819.07 (Blank)
Wide World Importers 1,901,956.66 (Blank)
--  The two branches of IF can use different datatypes for
--  their result. In that case, the IF function returns a
--  VARIANT datatype
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Product'[Brand] ),
    "Sales 1", [Sales Amount],
    "Sales 2",
        VAR SalesAmount = [Sales Amount]
        RETURN
            IF ( SalesAmount > 3000000, 3000000, "Less than 3K" )
)
ORDER BY 'Product'[Brand]
Brand Sales 1 Sales 2
A. Datum 2,096,184.64 Less than 3K
Adventure Works 4,011,112.28 3000000
Contoso 7,352,399.03 3000000
Fabrikam 5,554,015.73 3000000
Litware 3,255,704.03 3000000
Northwind Traders 1,040,552.13 Less than 3K
Proseware 2,546,144.16 Less than 3K
Southridge Video 1,384,413.85 Less than 3K
Tailspin Toys 325,042.42 Less than 3K
The Phone Company 1,123,819.07 Less than 3K
Wide World Importers 1,901,956.66 Less than 3K

IF returns a scalar value, it cannot be used to return a table.
DAX tries to convert the table to a scalar value, and it fails if there are multiple rows. The following code query an error.

EVALUATE
VAR SelectedYear = SELECTEDVALUE ( 'Date'[Calendar Year Number] )
VAR CurrentYear = YEAR ( TODAY () )
VAR Period = 
    IF ( 
        SelectedYear = CurrentYear,
        DATESYTD ( 'Date'[Date] ),      -- Table
        VALUES ( 'Date'[Date] )         -- Table
    )
VAR Result = CALCULATE ( [Sales Amount], Period ) 
RETURN { Result }

Related articles

Learn more about IF in the following articles:

Related functions

Other related functions are:

Last update: Mar 13, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://docs.microsoft.com/en-us/dax/if-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of IF? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.