cancel
Showing results 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

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

 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"

ELSE

2. If TableA(Activate)=False

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

2 REPLIES 2
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

Helper I

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!

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors