Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Complicated Conditional conditions Column

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?

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

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



     

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    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