Using Greater than to convert time above X to X

[Creature]

Hey All, esstentially I have 2 rows of data, one row is ticket type and one row is time worked on tickets in minutes. I need to convert tickets that were left open to a more reasonable value issue is I have multiple ticket types in the other row. So I need "When Ticket type = INC & Time_worked is greater than 1200 then = 1200" how would I go about doing this in power Bi?

[edhans]

IF(Table[INC & Time_Worked] > 1200, 1200)

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Creature]

I think I need to rephrase my question, Table Name is Supops, One column is Ticket Type which has INC, SCTASK,WRK in it, another column is time_worked with intergers in it. So I need if ticket type is INC and time_worked is greather than 1200 make it 1200. Sorry what you provided above does not work unless I did something wrong

[Greg_Deckler · Accepted solution]

@Creature Add a custom column with this formula:

if [Ticket Type] = "INC" and [time_worked] > 1200 then 1200 else [time_worked]

Remove the original column, rename the new column to the name of the original column.

[Creature]

Hey Greg, that worked, thanks! can you educate me on how I would add multiple variables? Lets say I also need same calc with time_worked= 0 then 1 or adding in other ticket types as well? basically how would I expand the formula? 

[Creature]

@Greg_Deckler can you see my questino above?

[Greg_Deckler]

@Creature Well, you could do something like this:

if [Ticket Type] = "INC" and [time_worked] IN { 0, 1, 2 } then 1200 else [time_worked]

Not sure if that is what you are asking.

[Creature]

So the same column has other ticket types that need to be modified as well - so the above formula makes all INC above 1200 = 1200 but what if I need to add more, SCTASK above 2000 = 2000 or INC below 1=1 etc into the same column with the above formula. @Greg_Deckler 

[Greg_Deckler]

@Creature Oh, I understand now. Well, unfortunately M doesn't have a case or switch statement so you are stuck with nested if statements. @ImkeF I think had some kind of way to simulate case/switch statements in Power Query so maybe she will stop by. Or @edhans might have a good solution for it. But in nested if land it would be something like:

if 
  [Ticket Type] = "INC" and [time_worked] > 1200 
  then 1200 
  else 
    if [Ticket Type[time_worked] = "STK" and [time_worked] = 2000
    then 2000
    else
      if ...

[Creature]

= Table.AddColumn(#"Extracted Text Before Delimiter", "Time Worked Rounded", each 
if [Ticket Type] = "INC" and [time_worked] > 1200 then 1200 
else [time_worked])

So this is what I currently have, I tried various ways of nesting and all returned an error. to make it easy for my solution Im trying to add and will need to do the same calc with other ticket types, tried your example didnt work or , didnt work etc

if [Ticket Type] = "INC" and [time_worked] < 1 then 1 else [time_worked]

Sorry not use to DAX at all. Use to doing these in DOMO.

[Creature]

@Greg_Deckler sorry to be annoying, just trying to get good data lol