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     🙂

TomMartens's avatar
TomMartens
Super User
6 years ago

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_'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!