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

9 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    _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])

  • 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",
""
)