Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

SWITCH Function to replace IF statement

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 di...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
johnt75
Super User
3 years ago

What is your measure definition now ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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