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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dukeof3arl
New Member

Help with converting IFS into a Custom Column

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.  

dukeof3arl_0-1697125543813.png

 

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

 

 

1 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>
)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Community Champion
Community Champion

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@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.

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

)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@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.

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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