How to check various values in another table and assess them before bringing them in

[aabi]

Hello everyone!

I have a small problem and I cannot get it done... I'm thinking that I might need to use a RELATED or 

LOOKUPVALUE but I cannot get it done.
 
In brief I have the following tables
 
Table A - Potential Clients

LeadID	Lead Days	Level	Activate
64639	80	Low	TRUE
64771	60	Medim	FALSE
64777	30	High	TRUE

Table B - Notes on Clients

Note ID	LeadID	Creation Date	Subject
175880	64639	11/12/2020	lvm
176154	64639	14/12/2020	2nd call
177521	64639	04/01/2021	First Contact
177684	64639	06/01/2021	Onboarded
179445	64639	22/01/2021	Call- LVM
177993	64771	08/01/2021	lvm
178444	64771	14/01/2021	Discovery
179146	64771	20/01/2021	Attended OB
180543	64771	01/02/2021	lvm
178842	64777	19/01/2021	Missed appt. email sent to reschedule

The relation is one (table A) to many (tableB)

Now I need preferably a calculated column in Table A, lets call it a "phase" , which will be doing something the following logic

1. If TableA(Activate)=False -> ok this part is easy:p

AND in TableB(Comments) does NOT equal "Discovery" -> this is what I'm not sure how to do. i.e. check only matching leadID's if there is not a comment called "Discovery"

Then = "Not Ready"

ELSE

2. If TableA(Activate)=False

AND in TableB(Comments) = "Discovery" for same leadID's

Then = "New"

ELSE

3. If TableA(Level)=medium or high

AND in TableB(Comments) is NOT "Discovery" or "Onboarded"

Then = "Jump"

There are various other statements to get my full list, but those are my main issues... how to check all comments in TABLE B before assessing what "phase" they are?

[amitchandak · Accepted solution]

@aabi , Check a new column example

Switch ( True() ,

TableA[Activate] = true() && countx(Filter(relatedtable(TableB), TableB[Comment]<> "Discovery"),TableB[LeadID]) +0 >0 , "Not Ready",
TableA[Activate] = false() && countx(Filter(relatedtable(TableB), TableB[Comment]= "Discovery"),TableB[LeadID]) +0 >0 , "New",
TableA[Level] in {"medium","high"} && countx(Filter(relatedtable(TableB), not(TableB[Comment] in {"Discovery" , "Onboarded"})),TableB[LeadID]) +0 >0 , "New",

)

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[aabi]

Hi @amitchandak

Thank you! I really appreciate this!

This is a really smart/interesting way of Power BI/DAX usage!

I have also updated it accordingly to add some more rules and it seems to do what I need!