Forum Discussion

_Aleksa_'s avatar
_Aleksa_
Helper II
6 years ago
Solved

Switch Formula Error

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...
Need Help
  • az38's avatar
    az38
    6 years ago

    _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",
""
)
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    _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_'s avatar
_Aleksa_
Helper II
6 years ago

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's avatar
az38
Community Champion
6 years ago

_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_'s avatar
    _Aleksa_
    Helper II
    6 years ago

    Thank you, this worked perfectly for a measure!!

     

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

     

    Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • nandukrishnavs's avatar
      nandukrishnavs
      Community Champion
      6 years ago

      _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_'s avatar
        _Aleksa_
        Helper II
        6 years ago

        Thank you!!!!