cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aabi
Frequent Visitor

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

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

 

LeadIDLead DaysLevelActivate
6463980LowTRUE
6477160MedimFALSE
6477730HighTRUE

 

Table B - Notes on Clients

 

Note IDLeadIDCreation DateSubject
1758806463911/12/2020lvm
1761546463914/12/20202nd call
1775216463904/01/2021First Contact
1776846463906/01/2021Onboarded
1794456463922/01/2021Call- LVM
1779936477108/01/2021lvm
1784446477114/01/2021Discovery
1791466477120/01/2021Attended OB
1805436477101/02/2021lvm
1788426477719/01/2021Missed 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?

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

aabi
Frequent Visitor

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors