Help with converting IFS into a Custom Column

[dukeof3arl]

Hey everyone,

I'm having an issue translating an excel formula into a custom column.  I'll illustrate below.

Here is the table for reference.  

Here is the excel formula:  =IFS(Target<0,(-(Actuals-Target)/Target)+1,Target>0,((Actuals-Target)/Target)+1,Target=0,(100%))

Basically if the actuals are zero then that's the highest desired attainment, capped at 200%.  However, if you hit your target you are "100%" of benchmark.  Anything exceeding the target will descend backwards from 99%.

Any help would be greatly appreciated!

-Luke

[Greg_Deckler]

@dukeof3arl I would recommend using a SWITCH(TRUE(), ... ) statement to replace this.

[dukeof3arl]

@Greg_Deckler - can you give me a pointer on the first part?  I'm unfamiliar with SWITCH or even how it would be used in this context.

[Greg_Deckler]

@dukeof3arl Sure:

SWITCH( TRUE(), 

  <logical condition 1>, <result if true>,

  <logical condition 2>, <result if true>,

  <logical condition 3>, <result if true>,

  ... (as many conditions/result pairs as you want)

  <result if no conditions are true>

)

[dukeof3arl]

@Greg_Deckler - Apologies - I'm doing this in PBI Desktop.  Did I post this in the wrong forum category?  Custom Column language doesn't recognize Switch.

[Greg_Deckler · Accepted solution]

@dukeof3arl ?? You can use SWITCH in a custom column.

Column = 
SWITCH( TRUE(), 
  <logical condition 1>, <result if true>,
  <logical condition 2>, <result if true>,
  <logical condition 3>, <result if true>,
  ... (as many conditions/result pairs as you want)
  <result if no conditions are true>
)

[dukeof3arl]

@Greg_Deckler - I just did it in measures.  But the result WAS using the switch function.   I trimmed the excel formula down a bit.

Attainment = SWITCH(TRUE(),[Targ] < 0,([Actual] - [Targ])*-1 / [Targ] +1,[Targ] = 0,1,"null")