SWITCH Function to replace IF statement

Question

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.&nbsp;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 + DAverageVAR UplusP = UAverage + PAverageVAR DplusP = DAverage + PAverageVAR CFAverage =&nbsp;&nbsp;&nbsp; IF(&nbsp; SELECTEDVALUE('Course'[CourseType] ) = "D", DAverage,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF(&nbsp; SELECTEDVALUE( 'Course'[CourseType] ) = "P", PAverage,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF(&nbsp; SELECTEDVALUE( 'Course'[CourseType] ) = "U", UAverage,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF(&nbsp; CONTAINS( VALUES( 'Course'[CourseType]),'Course'[CourseType], "U"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) &amp;&amp; CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "D"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;) &amp;&amp; CONTAINS( VALUES'( Course'[CourseType]), 'Course'[CourseType], "P"),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DAverage + PAverage + UAverage,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF(&nbsp; CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "D"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;) &amp;&amp; CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "P"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DplusP,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF( CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "D"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;) &amp;&amp; CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "U"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;), UplusD,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IF( CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "U"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;) &amp;&amp; CONTAINS( VALUES( 'Course'[CourseType]), 'Course'[CourseType], "P"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;), UplusP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )))))))RETURNCFAverage&nbsp;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 + DAverageVAR UplusP = UAverage + PAverageVAR DplusP = DAverage + PAverageVAR CourseType = SELECTEDVALUE( 'Course'[CourseType] )VAR CFAverage = SWITCH( TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CourseType = "D", DAverage,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CourseType = "P", PAverage,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CourseType = "U", UAverage,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CourseType = "U" &amp;&amp; CourseType = "D", UplusD,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CourseType = "U" &amp;&amp; CourseType = "P", UplusP,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CourseType = "P" &amp;&amp; CourseType = "D", DplusP,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DAverage + PAverage + UAverage)&nbsp;Any advice on where I am going wrong, or whether this is possible using SWITCH gratefully received, thank you!

johnt75 · Answer

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
            &amp;&amp; NumChosenCourses = 1, DAverage,
        "D"
            IN ChosenCourses
            &amp;&amp; "P"
            IN ChosenCourses
            &amp;&amp; NumChosenCourses = 2, DPlusP
    )
RETURN
    Result

anonymous · Answer

Thanks&nbsp;johnt75,&nbsp;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 · Answer

What is your measure definition now ?

anonymous · Answer

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 + DAverageVAR UplusP = UAverage + PAverageVAR DplusP = DAverage + PAverageVAR CFAverage = SWITCH( TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"D" IN CourseType &amp;&amp;&nbsp;NumberofCourseType = 1,&nbsp;DAverage&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"P" IN CourseType &amp;&amp;&nbsp;NumberofCourseType = 1,&nbsp;PAverage&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"U" IN CourseType &amp;&amp;&nbsp;NumberofCourseType = 1, UAverage&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"D" IN CourseType &amp;&amp;&nbsp;"P" IN CourseType &amp;&amp;&nbsp;"U" IN CourseType &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NumberofCourseType = 3, DAverage + PAverage + UAverage,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"D" IN CourseType &amp;&amp;&nbsp;"P" IN CourseType &amp;&amp;&nbsp;NumberofCourseType =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2, DplusP,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"D" IN CourseType &amp;&amp;&nbsp;"U" IN CourseType &amp;&amp;&nbsp;NumberofCourseType =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2, UplusD,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"U" IN CourseType &amp;&amp;&nbsp;"P" IN CourseType &amp;&amp;&nbsp;NumberofCourseType =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2, UplusP)RETURN&nbsp;CFAverage

Forum Discussion

SWITCH Function to replace IF statement

4 Replies

Related content

Replace null with if statement

Question on Replace Function

replace values in columns that start the same using if statements

OR Statement ?

Replace NULL