Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
_Aleksa_
Helper II
Helper II

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!!
2 ACCEPTED SOLUTIONS
az38
Community Champion
Community Champion

@_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",
""
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

@_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
🙂


Regards,
Nandu Krishna

View solution in original post

9 REPLIES 9
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
Community Champion
Community Champion

@_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",
""
)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Thank you, this worked perfectly for a measure!!

 

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

 

Thanks!

@_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
🙂


Regards,
Nandu Krishna

Thank you!!!!

Anonymous
Not applicable

Hi @_Aleksa_ ,

 

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

 

Best Regards,

Jay

Anonymous
Not applicable

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
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.