Issue with Switch statement

[Redraidas1]

Does anybody know why I'm getting this error message for the below SWITCH statement and how to make it work? Thanks!

Error: 'The expression specified in the query is not a valid table expression'

Measure =

SWITCH (

TRUE(),

'Conversion Rates v2'[M_Conversion] = 1, GENERATESERIES(0, 10000, 10),

'Conversion Rates v2'[M_Conversion] <1, GENERATESERIES(0, 100000, 100),

'Conversion Rates v2'[M_Conversion] <0.1, GENERATESERIES(0, 1000000, 1000),

'Conversion Rates v2'[M_Conversion] <0.001, GENERATESERIES(0, 10000000, 10000)

)

[v-kelly-msft · Accepted solution]

Hi @Redraidas1 ，

First create a table as below:

Table = GENERATESERIES(0, 10000000, 10)

Then create a measure as below:

measure = 
SWITCH (
TRUE(),
SELECTEDVALUE('Conversion Rates v2'[M_Conversion]) <0.001, 
IF(MOD(MAX('Table'[Value]),10000)=0&&MAX('Table'[Value])<=10000000,MAX('Table'[Value]),BLANK()),
SELECTEDVALUE('Conversion Rates v2'[M_Conversion]) <0.1, 
IF(MOD(MAX('Table'[Value]),1000)=0&&MAX('Table'[Value])<=1000000,MAX('Table'[Value]),BLANK()),
SELECTEDVALUE('Conversion Rates v2'[M_Conversion])<1,
IF(MOD(MAX('Table'[Value]),100)=0&&MAX('Table'[Value])<=100000,MAX('Table'[Value]),BLANK()),
SELECTEDVALUE('Conversion Rates v2'[M_Conversion])=1,
IF(MOD(MAX('Table'[Value]),10)=0&&MAX('Table'[Value])<=10000,MAX('Table'[Value]),BLANK()))

Put the measure in the filter field>choose "is not blank":

And you will see:

For my sample .pbix file,pls see attached.

Best Regards,
Kelly

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

[AlB]

@Redraidas1 

Can you share the pbix?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Redraidas1 

Remember you have no row context if you are using this in a visual. Depending on your filter context, you'll need something like:

Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Conversion Rates v2'[M_Conversion] ) = 1, GENERATESERIES ( 0, 10000, 10 ),
    SELECTEDVALUE ( 'Conversion Rates v2'[M_Conversion] ) < 1, GENERATESERIES ( 0, 100000, 100 ),
    etc.
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Redraidas1]

Just changed it as per you suggestion - unfortunately still getting the same error.