Switch Formula Error

[_Aleksa_]

Hello,

I am using the switch formula and I am getting the following error:

"A single value for column "Pmt_Instruction_Cde" cannot be determined."

I am puzzled because I am using exactly the same criteria with just different number of days in another measure and it works perfectly fine there.

 Ind = SWITCH( TRUE(),

RIGHT('Weekly Data'[Pmt_Instruction_Cde],1)="2" && 'Weekly Data'[Days from Report Date] >=0 && 'Weekly Data'[Days from Report Date] <31,"All States Overdue",

RIGHT('Weekly Data'[Pmt_Instruction_Cde],1)="2" && 'Weekly Data'[State]="CA" && 'Weekly Data'[Days from Report Date] >=90,"Overdue",

RIGHT('Weekly Data'[Pmt_Instruction_Cde],1)="2" && 'Weekly Data'[State]="OR" && 'Weekly Data'[Days from Report Date] >=60,"OR Overdue",

""

)

Thanks!!

[az38 · Accepted solution]

@_Aleksa_ 

if you need a measure try

 Ind = 
var _Pmt_Instruction_Cde = MAX('Weekly Data'[Pmt_Instruction_Cde])
var _Days = MAX('Weekly Data'[Days from Report Date])
var _State = MAX('Weekly Data'[State]) 

RETURN

SWITCH( TRUE(),
RIGHT(_Pmt_Instruction_Cde, 1)="2" && _Days  >=0 && _Days  <31, "All States Overdue",
RIGHT(_Pmt_Instruction_Cde, 1)="2" && _State ="CA" && _Days  >=90,"Overdue",
RIGHT(_Pmt_Instruction_Cde, 1)="2" && _State ="OR" && _Days  >=60,"OR Overdue",
""
)

[TomMartens]

Hey @_Aleksa_ ,

what is the context of the DAX statement, are you creating

• a calculated column, or
• a measure

If you are creating a measure, and there is no row context than you have to wrap the column references inside an aggregation function like MAX.

Hopefully, this provides some new insights and helps to tackle your challenge.

Regards,

Tom

[_Aleksa_]

I was creating a measure orginally.

When I tried creating a solumn with the same statement it teruned only one result ratehr than 3 was I intended.

Also, when I incorporate MAX  function it is giving me another error message saying:
"Too many statements were passed to the MAX function."

Thank you for the help!

[az38 · Accepted solution]

@_Aleksa_ 

if you need a measure try

 Ind = 
var _Pmt_Instruction_Cde = MAX('Weekly Data'[Pmt_Instruction_Cde])
var _Days = MAX('Weekly Data'[Days from Report Date])
var _State = MAX('Weekly Data'[State]) 

RETURN

SWITCH( TRUE(),
RIGHT(_Pmt_Instruction_Cde, 1)="2" && _Days  >=0 && _Days  <31, "All States Overdue",
RIGHT(_Pmt_Instruction_Cde, 1)="2" && _State ="CA" && _Days  >=90,"Overdue",
RIGHT(_Pmt_Instruction_Cde, 1)="2" && _State ="OR" && _Days  >=60,"OR Overdue",
""
)

[_Aleksa_]

Thank you, this worked perfectly for a measure!!

Woudl you be able to provide an option for a column?

Thanks!

[nandukrishnavs · Accepted solution]

@_Aleksa_ 

Try this 

Ind =
SWITCH (
    TRUE (),
    RIGHT (
        'Weekly Data'[Pmt_Instruction_Cde],
        1
    ) = "2"
        && 'Weekly Data'[Days from Report Date] = 0
        && 'Weekly Data'[Days from Report Date] < 31, "All States Overdue",
    RIGHT (
        'Weekly Data'[Pmt_Instruction_Cde],
        1
    ) = "2"
        && 'Weekly Data'[State] = "CA"
        && 'Weekly Data'[Days from Report Date] >= 90, "Overdue",
    RIGHT (
        'Weekly Data'[Pmt_Instruction_Cde],
        1
    ) = "2"
        && 'Weekly Data'[State] = "OR"
        && 'Weekly Data'[Days from Report Date] >= 60, "OR Overdue",
    ""
)

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[_Aleksa_]

Thank you!!!!

[v-jayw-msft]

Hi @_Aleksa_ ,

If the answer solved your problem, please consider accept it as a solution.

Best Regards,

Jay

[v-jayw-msft]

Hi @_Aleksa_ ,

You will no need to use MAX() or SELECTEDVALUE() function to create column. Just use your or orginal code will work.

Best Regards,

Jay

[az38]

@_Aleksa_ 

are you sure you need a measure?

you can create a column with the same statement or use SELECTEDVALUE() like 

SELECTEDVALUE('Weekly Data'[Pmt_Instruction_Cde])