0

I'm trying to create validation based on date and some filters

my input table is

Status  Type    Date        PolicyNo 
PS      T607    01-01-2020  1002
PS      T608    01-01-2020  1002
CF      T646    01-01-2020  1002
PS      T607    04-01-2020  1003

My condition is

1) In a single day how to apply multiple conditions

  • Ex. 01-01-2020 on day 1002 Policy(1002) we have three Type T607 with any one of (T608/T646) with status (PS/CF) the output value could be 0 otherwise 1

2) My expected output is 

Status  Type    Date        PolicyNo    Accept
PS      T607    01-01-2020  1002        0
PS      T608    01-01-2020  1002        0
CF      T646    01-01-2020  1002        0
PS      T607    04-01-2020  1003        1

EDIT:

Date
01-01-2020
01-01-2020
01-01-2020

PolicyNo
1002
1002
1002

Type : T697 with (T608 or T646)

T607 - compalsory so (&&)
T608 - Optional so (||)
T646 - Optional so 

(and)

Status : PS or CF 
PS - Optional so (||)
CF - Optional

Conclude Condition: Same date (ex.01-01-2020) and Same PolicyNo(ex.1002) with (Type: T697 with (T608 or T646)) with (Status: PS or CF)

KARTHIKEYAN.A
  • 18,210
  • 6
  • 124
  • 133

1 Answers1

1

Multiple conditions in M (Power Query) for a custom column:

= if [Date] = Date.From(DateTime.LocalNow()) and [Type] = "T607" and [PolicyNo] = 1003 then 1 else 0

And so on...

Note: The syntax has to be lower case, becaue M is case sensitive. You also can stack the if´s or use else if´s. You can also use a or condition.

You can do the same in DAX thou. With IF() and OR() functions (as new column):

= IF(OR([Date] = TODAY(), [Type] = "T607", [PolicyNo] = 1003), 1, 0)

EDIT

To your 4th comment. This logik works just fine (simplified sample):

enter image description here

Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • I'm working with dynamic data. Date and PolicyNo are dynamic, Type T697 with any one of (T608/T646) with status (PS/CF) this condition is static. – KARTHIKEYAN.A Jan 21 '20 at 08:40
  • ```WITCH(TRUE(),'DatewithOtherConditions'[type] = "T607" && 'DatewithOtherConditions'[type] = "T608" || 'DatewithOtherConditions'[type] = "T646" && 'DatewithOtherConditions'[status] = "CF" || 'DatewithOtherConditions'[status] = "CF" && DatewithOtherConditions[Date] ,0,1)``` i'm writing this code getting wrong result. – KARTHIKEYAN.A Jan 21 '20 at 08:42
  • What i'm expecting is ```((Value1 && (Value2 || Value3)) && (Value4 || Value5))``` depends on PolicyNo and Running Dates. – KARTHIKEYAN.A Jan 21 '20 at 08:46
  • ```if (([Type] = "T607" and ([Type] = "T608" or [Type] = "T646")) and ([Status] = "PS" or [Status] = "CF")) then 0 else 1``` also not working producing wrong result. The condition is Date and PolicyNo. Same day with same PolicyNo the above condition should satisfy – KARTHIKEYAN.A Jan 21 '20 at 08:53
  • Your question and comments are to confusing^^ Based on that I have no clue how your final condition should look like. You can try to post your final condition in steps or in a simpliefied way. Like that maybe: `(Value=1 && (Value = 2 || Value2 = 3)) and Date = PolicyNo` – Strawberryshrub Jan 21 '20 at 09:32