Can I not return a table with an if statement?

[rbreneman]

Hi!

I'm trying to use an if statement inside of a measure to determine which of two different generateseries functions to run. This is based on whether or not the parameter is negative. I'm getting an error that states "The function expects a table expression for argument, but a string or numeric expression was used." I've also tried to use the SIGN function instead of > 0 but it gives the same error. I also tried SWITCH instead of IF, but again, same error.

The measure works perfectly if I replace "NumTestedRange" in the FilteredTbl variable with "PositiveNumTestedRange" or "NegativeNumTestedRange".

I can't seem to get a single generateseries function to work because if I provide a negative value in the "SwingCriteria" parameter then my series would look like 100...80 when I need to flip it and have it be 80...100. Can someone help me figure out a workaround here?

test = 
    VAR SelectedSchool = SELECTEDVALUE(AssessmentData[BEDSCODE])
    VAR PositiveNumTestedRange = GENERATESERIES( 'My Measures'[SelectedSchoolNumTested], 'My Measures'[SelectedSchoolNumTested] + ('My Measures'[SelectedSchoolNumTested] * SwingCriteria[SwingCriteria Value]) )
    VAR NegativeNumTestedRange = GENERATESERIES( 'My Measures'[SelectedSchoolNumTested] + ('My Measures'[SelectedSchoolNumTested] * SwingCriteria[SwingCriteria Value]), 'My Measures'[SelectedSchoolNumTested] )
    VAR NumTestedRange = IF( SwingCriteria[SwingCriteria Value] > 0, PositiveNumTestedRange, NegativeNumTestedRange )

    VAR FilteredTbl = CALCULATETABLE(AssessmentData,
        ALL(AssessmentData),
        AssessmentData[BEDSCODE] <> SelectedSchool,
        AssessmentData[NUM_TESTED] IN NumTestedRange
    )

RETURN
COUNTROWS(FilteredTbl)

[DataInsights · Accepted solution]

@rbreneman,

Since IF and SWITCH return scalar values, you need to shift the IF logic to start/end variables and then use these variables in GENERATESERIES:

test =
VAR SelectedSchool =
    SELECTEDVALUE ( AssessmentData[BEDSCODE] )
VAR SeriesStart =
    IF (
        SwingCriteria[SwingCriteria Value] > 0,
        [SelectedSchoolNumTested],
        [SelectedSchoolNumTested] + ( [SelectedSchoolNumTested] * SwingCriteria[SwingCriteria Value] )
    )
VAR SeriesEnd =
    IF (
        SwingCriteria[SwingCriteria Value] > 0,
        [SelectedSchoolNumTested] + ( [SelectedSchoolNumTested] * SwingCriteria[SwingCriteria Value] ),
        [SelectedSchoolNumTested]
    )
VAR NumTestedRange =
    GENERATESERIES ( SeriesStart, SeriesEnd )
VAR FilteredTbl =
    CALCULATETABLE (
        AssessmentData,
        ALL ( AssessmentData ),
        AssessmentData[BEDSCODE] <> SelectedSchool,
        AssessmentData[NUM_TESTED] IN NumTestedRange
    )
RETURN
    COUNTROWS ( FilteredTbl )

[rbreneman]

@DataInsights ,

This worked perfectly. Thank you so much! I tried various combinations but for some reason never thought to try that particular one. I appreciate you taking the time to help and explain why what I was trying to do wasn't working!

Thanks again!

[DataInsights]

Glad to hear that worked!