SWITCH Function to replace IF statement

[Anonymous]

Hi, I have a long nested IF statement which I would like to use SWITCH for instead to cut down my rows of code, but am stuck as to how to switch a combination of values.

My IF statement allows different calculations based on which values are selected in the filter (can be D, P or U, or combinations of these):

VAR DAverage = ROUNDUP( DIVIDE( ( [CF -1 D] + [CF -2 D] ), 2 ), 0 )

VAR PAverage = ROUNDUP( DIVIDE( ( [CF -1 P] + [CF -2 P]), 2 ), 0 )

VAR UAverage = ROUNDUP( DIVIDE( ( [CF -1 U] + [CF -2 U]), 2 ), 0 )

VAR UplusD = UAverage + DAverage

VAR UplusP = UAverage + PAverage

VAR DplusP = DAverage + PAverage

VAR CFAverage =

    IF(  SELECTEDVALUE('Course'[CourseType] ) = "D", DAverage,

        IF(  SELECTEDVALUE( 'Course'[CourseType] ) = "P", PAverage,

            IF(  SELECTEDVALUE( 'Course'[CourseType] ) = "U", UAverage,

                IF(  CONTAINS( VALUES( 'Course'[CourseType]),'Course'[CourseType], "U"

                        ) && CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "D"                       

                               ) && CONTAINS( VALUES'( Course'[CourseType]), 'Course'[CourseType], "P"),

                    DAverage + PAverage + UAverage,

                    IF(  CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "D"

                         ) && CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "P"),

                        DplusP,

                        IF( CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "D"

                               ) && CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "U"

                               ), UplusD,

                            IF( CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "U"

                                   ) && CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "P"

                                   ), UplusP

                            )))))))

RETURN

CFAverage

I have tried to re-create this using SWITCH, but when I change the filters to, for example, D and P combination I get the sum of D+P+U instead:

VAR DAverage = ROUNDUP( DIVIDE( ( [CF -1 D] + [CF -2 D] ), 2 ), 0 )

VAR PAverage = ROUNDUP( DIVIDE( ( [CF -1 P] + [CF -2 P]), 2 ), 0 )

VAR UAverage = ROUNDUP( DIVIDE( ( [CF -1 U] + [CF -2 U]), 2 ), 0 )

VAR UplusD = UAverage + DAverage

VAR UplusP = UAverage + PAverage

VAR DplusP = DAverage + PAverage

VAR CourseType = SELECTEDVALUE( 'Course'[CourseType] )

VAR CFAverage = SWITCH( TRUE(),

                               CourseType = "D", DAverage,
                               CourseType = "P", PAverage,
                               CourseType = "U", UAverage,

                               CourseType = "U" && CourseType = "D", UplusD,

                               CourseType = "U" && CourseType = "P", UplusP,

                               CourseType = "P" && CourseType = "D", DplusP,

                               DAverage + PAverage + UAverage

)

Any advice on where I am going wrong, or whether this is possible using SWITCH gratefully received, thank you!

[johnt75]

The problem is that SELECTEDVALUE will only return a value if there is a single selection. If there are multiple selections then it will return blank.

You'll need to use a combination of VALUES and COUNTROWS to determine which condition matches, e.g.

My Measure =
VAR ChosenCourses =
    VALUES ( 'Course'[Course Type] )
VAR NumChosenCourses =
    COUNTROWS ( ChosenCourses )
VAR Result =
    SWITCH (
        TRUE (),
        "D"
            IN ChosenCourses
            && NumChosenCourses = 1, DAverage,
        "D"
            IN ChosenCourses
            && "P"
            IN ChosenCourses
            && NumChosenCourses = 2, DPlusP
    )
RETURN
    Result

[Anonymous]

Thanks @johnt75, unfortunately this now returns as blank when I use a combination of D/P/U in the filter - the correct data are still returned on a single selection.

[johnt75]

What is your measure definition now ?

[Anonymous]

So my measure now looks like this:

VAR CourseType = VALUES( 'Course'[CourseType] )
VAR NumberofCourseType = COUNTROWS( CourseType)

VAR DAverage = ROUNDUP( DIVIDE( ( [CF -1 D] + [CF -2 D] ), 2 ), 0 )

VAR PAverage = ROUNDUP( DIVIDE( ( [CF -1 P] + [CF -2 P]), 2 ), 0 )

VAR UAverage = ROUNDUP( DIVIDE( ( [CF -1 U] + [CF -2 U]), 2 ), 0 )

VAR UplusD = UAverage + DAverage

VAR UplusP = UAverage + PAverage

VAR DplusP = DAverage + PAverage

VAR CFAverage = SWITCH( TRUE(),

                             "D" IN CourseType && NumberofCourseType = 1, DAverage

                             "P" IN CourseType && NumberofCourseType = 1, PAverage

                             "U" IN CourseType && NumberofCourseType = 1, UAverage

                             "D" IN CourseType && "P" IN CourseType && "U" IN CourseType &&                                                 NumberofCourseType = 3, DAverage + PAverage + UAverage,

                             "D" IN CourseType && "P" IN CourseType && NumberofCourseType =                                                2, DplusP,

                             "D" IN CourseType && "U" IN CourseType && NumberofCourseType =                                                2, UplusD,

                             "U" IN CourseType && "P" IN CourseType && NumberofCourseType =                                                2, UplusP)

RETURN CFAverage