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

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'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