Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aabi
Helper I
Helper I

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
Super User

@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

 

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

 

 

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.