Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbreneman
Helper II
Helper II

Can I not return a table with an if statement?

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)

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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 )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DataInsights
Super User
Super User

@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 )

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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!

Glad to hear that worked!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.