Complicated Conditional conditions Column

[Anonymous]

I have a bit complex goal I want to achieve, not sure if its possible at all.

I want to include a custom column that has a number of conditions based on vairus fields. 

If ([Date] = (april 26th 2021) and [Time] between (1:30 am and 8:30 Am) and [SLA Met]=0 and [Service Desk]="EUC" then "OK" else "Not OK"

Is something like this possible?

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Yes, possible in both Power Query and DAX, but the implementation largely depends on whether your fields are all in the same table or not.

Assuming that they are, go to Add Column tab in Power Query, select Custom Column, then enter something like the following formula in the calculation window. I'd recommend doing this in Power Query as DAX calculated columns should only really be used as an absolute last resort due to the memory use they require.

if [Date] = #date(2021, 26, 04)
  and [Time] >= #time(01, 30, 00)
  and [Time] <= #time(08, 30, 00)
  and [SLA Met] = 0
  and [Service Desk] = "EUC"
then "OK"
else "Not OK"

If they aren't in the same table, you will either need to merge your tables in Power Query before adding this column, or you will need to create the column in DAX, using RELATED() to reference fields that aren't in the same table (presuming that there is a suitable relationship in place between the tables).

Pete

[Fowmy]

@Anonymous 
Add the following New Column to your table in the model (NOT power Query): 

New Column =
IF (
    [Date] = DATE ( 2021, 4, 26 )
        && (
            [Time] >= TIME ( 1, 30, 0 )
                && [Time] <= TIME ( 8, 30, 0 )
        )
        && [SLA Met] = 0
        && [Service Desk] = "EUC",
    "OK",
    "Not OK"
)

[Anonymous]

Sure mate! You can do it on Power Query: Add Custom Column. There you can apply all the conditions you want. Just be careful to separate the statements with brackets and commas. Also, since you are going to provide specific values, ensure to type the exact value since it is case & string sensitive.