SWITCH DAX Function (Logical)
Returns different results depending on the value of an expression.
Syntax
Parameter | Attributes | Description |
---|---|---|
Expression |
The expression to be evaluated. |
|
Value | Repeatable |
If expression has this value the corresponding result will be returned. |
Result | Repeatable |
The result to be returned if Expression has corresponding value. |
Else | Optional |
If there are no matching values the Else value is returned. |
Return values
A scalar value coming from one of the Result expressions, if there was a match with Value, or from the Else expression, if there was no match with any Value.
Remarks
All result expressions and the else expression must be of the same data type.
A common use of SWITCH is to match the result of an expression with constant value:
SWITCH ( [A], 0, "Zero", 1, "One", 2, "Two", "Other numbers" )
However, the
By using TRUE as a first argument, SWITCH can replace a list of cascading IF statements.
The following code:
IF ( [A] > [B], "First case", IF ( [A] = [B], "Second case", IF ( [A] = 0, "Third case", "Fourth case" ) ) )
can be written as:
SWITCH ( TRUE, [A] > [B], "First case", [A] = [B], "Second case", [A] = 0, "Third case", "Fourth case" )
» 3 related functions
Examples
-- SWITCH evaluates its first argument (value) and then uses the next -- remaining parameters in pair: the first element is used to match the value -- the second as the result if there is a match. -- The last argument, alone, provides the no-match value and it defaults -- to blank. DEFINE MEASURE Sales[Discounted Sales] = SUMX ( VALUES ( 'Product'[Category] ), VAR DiscountPct = SWITCH ( 'Product'[Category], "Audio", 0.15, "Computers", 0.2, "Cell phones", 0.13, 0 ) RETURN [Sales Amount] * (1 - DiscountPct ) ) EVALUATE SUMMARIZECOLUMNS ( 'Product'[Category], "Sales Amount", [Sales Amount], "Discounted sales", [Discounted Sales] ) ORDER BY [Category]
Product[Category] | Sales Amount | Discounted sales |
---|---|---|
Audio | 384,518.16 | 326,840.44 |
Cameras and camcorders | 7,192,581.95 | 7,192,581.95 |
Cell phones | 1,604,610.26 | 1,396,010.93 |
Computers | 6,741,548.73 | 5,393,238.98 |
Games and Toys | 360,652.81 | 360,652.81 |
Home Appliances | 9,600,457.04 | 9,600,457.04 |
Music, Movies and Audio Books | 314,206.74 | 314,206.74 |
TV and Video | 4,392,768.29 | 4,392,768.29 |
-- A common usage of SWITCH is to use a constant as the value argument -- and expressions in the pairs. This technique allows more flexibility -- even though it somewhat lacks in readability. DEFINE MEASURE Sales[Discounted Sales] = SUMX ( SUMMARIZE ( Sales, Sales[Net Price], Product[Category] ), VAR DiscountPct = SWITCH ( TRUE, Sales[Net Price] <= 150, 0.15, Sales[Net Price] <= 1000, 0.2, Product[Category] = "Audio", 0.13, 0 ) RETURN [Sales Amount] * (1 - DiscountPct ) ) EVALUATE SUMMARIZECOLUMNS ( 'Product'[Category], "Sales Amount", [Sales Amount], "Discounted sales", [Discounted Sales] ) ORDER BY [Category]
Product[Category] | Sales Amount | Discounted sales |
---|---|---|
Audio | 384,518.16 | 319,255.67 |
Cameras and camcorders | 7,192,581.95 | 5,975,444.25 |
Cell phones | 1,604,610.26 | 1,302,864.23 |
Computers | 6,741,548.73 | 5,806,084.78 |
Games and Toys | 360,652.81 | 306,554.89 |
Home Appliances | 9,600,457.04 | 8,540,512.07 |
Music, Movies and Audio Books | 314,206.74 | 255,356.90 |
TV and Video | 4,392,768.29 | 3,561,271.37 |
-- Using SWITCH the first condition met defines the result. -- In the following example, the second condition (<= 150) will never be -- met, because the first one is less restrictive. DEFINE MEASURE Sales[Discounted Sales] = SUMX ( SUMMARIZE ( Sales, Sales[Net Price], Product[Category] ), VAR DiscountPct = SWITCH ( TRUE, Sales[Net Price] <= 1000, 0.2, Sales[Net Price] <= 150, 0.15, Product[Category] = "Audio", 0.13, 0 ) RETURN [Sales Amount] * (1 - DiscountPct ) ) EVALUATE SUMMARIZECOLUMNS ( 'Product'[Category], "Sales Amount", [Sales Amount], "Discounted sales", [Discounted Sales] ) ORDER BY [Category]
Product[Category] | Sales Amount | Discounted sales |
---|---|---|
Audio | 384,518.16 | 307,614.53 |
Cameras and camcorders | 7,192,581.95 | 5,961,597.72 |
Cell phones | 1,604,610.26 | 1,283,688.21 |
Computers | 6,741,548.73 | 5,765,868.70 |
Games and Toys | 360,652.81 | 288,522.25 |
Home Appliances | 9,600,457.04 | 8,489,439.38 |
Music, Movies and Audio Books | 314,206.74 | 251,365.39 |
TV and Video | 4,392,768.29 | 3,537,902.55 |
Related articles
Learn more about SWITCH in the following articles:
-
Optimizing mutually exclusive calculations
This article describes how to optimize DAX expressions with mutually exclusive calculations that might cause slow query performance. » Read more
-
Optimizing IF and SWITCH expressions using variables
This article describes how variables should be used in DAX expressions involving IF and SWITCH statements in order to improve performance. » Read more
-
Using field parameters and calculation groups for conditional formatting
This article describes how to apply conditional formatting on measures picked from a slicer and implemented using two techniques: field parameters and calculation groups. » Read more
-
Optimizing SWITCH on slicer selection with Group By Columns
This article describes how to use the Group By Columns property to store the slicer selection by using the same column used in a SWITCH function to optimize the query performance. » Read more
-
Understanding the optimization of SWITCH
The SWITCH function in DAX has been optimized over the years, and it is helpful to know what makes the optimization work best. » Read more
Related functions
Other related functions are:
Last update: Oct 3, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/switch-function-dax