Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Custom Column else if divide

Current column is [Days to Close] new custom column is Avg. Days Worked I would the like formula to say if [days to close] is greater than or equal to 181 then 90 and if [days to close] is less ...

lit2018pbi
6 years ago
Anonymous
Create a new column using the following DAX
custom column = IF('Table'[Days] > 501, [Days]/4,IF( [Days]<=500 && 'Table'[Days] >= 180, [Days]/3, IF([Days]<180, [Days]/2)))

Anonymous

Not applicable

6 years ago

Hi Anonymous ,

In M Code

Custom Column=if [Days to Close]>=181 then 90 else if [Days to Close]<=180 then [Days to Close]/ 2

In DAX (Calculated Column)

Column =
SWITCH(
TRUE(),
Table[Days to Close] >= 181 , 90,
DIVIDE(Table[Days to CLose],2)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable
6 years ago
Anonymous Thank you, I am using M code. My formula changed and this is what I have entered. I am still getting the "Token Else expected" error.

custom column=if[Days to Close]>501 then [Days to Close]/4 else if [Days to Close]<=500 then [Days to Close]/3 else if [Days to Close]<180 then [Days to Close]/2

Do you think this would be easier in DAX?
- lit2018pbi
  Resolver II
  6 years ago
  Anonymous
  Create a new column using the following DAX
  custom column = IF('Table'[Days] > 501, [Days]/4,IF( [Days]<=500 && 'Table'[Days] >= 180, [Days]/3, IF([Days]<180, [Days]/2)))
- Anonymous
  Not applicable
  6 years ago
  Hi Anonymous .
  
  Use this for M Code.
  
  if [Days to Close]<180 then [Days to Close]/2 else if [Days to Close]<=500 then [Days to Close]/3 else [Days to Close]/4
  
  Additionally, you can use add contional columns
  
  To nest an if within another if we use the same if – then – else methodology, as shown below
  
  = if [thing to to test #1] = "something else" then [do this if true] else if [thing to to test #2] = "something else" then [do this if true] else [do this if false]
  
  https://exceloffthegrid.com/power-query-if-statements-for-conditional-logic/
  
  Regards,
  Harsh Nathani
  Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Related content