Search if a value appears in a column of an other table

[Ope_AF]

Hi Everybody,

I need your help on an issue that i encountered :

I have two tables : Table 1 (projects are unique)

Project	Status
AM-007	BAU
FM-006	Terminated
AMFM-001	On Going

Table 2 (project can appears mutliple time)

Project	Date	Status
AM-007	null
AM-007	30/08/2020
AM-007	31/08/2020
CT-036	null
CT-036	30/08/2020

I need to fill the status colum of the second table. In order to do this i have rules :

- if date = null then i search on the first table if the project is in and if it is i copy the status of the project of the table 1 in the table 2. If the project is not in the table 1 then i put the status "N/A"

- else if the date is outdated i put the status "Delivered" else the status "On Going"

For example in the exemple above it would give this :

Project	Date	Status
AM-007	null	BAU
AM-007	30/08/2020	Delivered
AM-007	31/08/2020	On Going
CT-036	null	N/A
CT-036	30/08/2020	On Going

i've tried to put a custom column with this formula : if Table2[Date]=null then if Table2[Project] = Table1[Project] then Table2[Status] = Table1[Status] else "N/A" else "To be fixed"
Actually it doesn't works, all the column got the value "N/A". I don't even know what do power querry did when i do "if Table2[Project] = Table1[Project]" 

Could you please help me, i hope being clear.

Best Regards 

[Anonymous · Accepted solution]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTVNTAwV9JRUorVQeIZG+gbWOgbGRgZoIob6htYIoSdQ3QNjM1gmuE8ZM2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CA_Num = _t, F_ABOLIGNE.DateDebut = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CA_Num", type text}, {"F_ABOLIGNE.DateDebut", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each   try if [F_ABOLIGNE.DateDebut]= null then Project_PM_Alloc{[PROJECT REF=[CA_Num]]}[Status] else if [F_ABOLIGNE.DateDebut]> Date.From( DateTime.LocalNow()) then "On Going" else "Delivered" otherwise "N/A")
in
    #"Added Custom"

@Ope_AF 

edited to match your label

[Anonymous · Accepted solution]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTVNTAwV9JRUorVQeIZG+gbWOgbGRgZoIob6htYIoSdQ3QNjM1gmuE8ZM2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CA_Num = _t, F_ABOLIGNE.DateDebut = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CA_Num", type text}, {"F_ABOLIGNE.DateDebut", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each   try if [F_ABOLIGNE.DateDebut]= null then Project_PM_Alloc{[PROJECT REF=[CA_Num]]}[Status] else if [F_ABOLIGNE.DateDebut]> Date.From( DateTime.LocalNow()) then "On Going" else "Delivered" otherwise "N/A")
in
    #"Added Custom"

@Ope_AF 

edited to match your label

[Ope_AF · Accepted solution]

I tried this but it doesn't works actually the real name is :

T1 : Project_PM_Alloc

T2: F_ABOENTETE

date : F_ABOLIGNE.DateDebut

project in T1 : PROJECT REF

project in T2 : CA_Num

Could you update the formula with this name ?
Thank you very much for your answer
Best Regards

[Anonymous]

or, using the null coalescing operator, instead try ... otherwise

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTVNTAwV9JRUorVQeIZG+gbWOgbGRgZoIob6htYIoSdQ3QNjM1gmuE8ZM2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each  if [Date]= null then (T1{[Project=[Project]]}?[Status]?)??"N/A" else if [Date]> Date.From( DateTime.LocalNow()) then "On Going" else "Delivered")
in
    #"Added Custom"

[Greg_Deckler]

@Ope_AF - You should be able to do something like this:

Column = 
  VAR __Status = LOOKUPVALUE('Table1'[Status],'Table1'[Project],'Table2'[Project])
RETURN
  SWITCH(TRUE(),
    ISBLANK([Date]) && ISBLANK(__Status),"N/A",
    ISBLANK([Date]),__Status,
    <additional logic goes here>
  )

Not sure I fully understand your "outdated" logic. 

[Ope_AF]

I'm not really confortable with DAX Language so i don't understand well your solution but thanks,
I mean the day is in the past by "outdated"
Best regards