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

Looking up multiple associated values in related table and filtering conditionally

Hi everyone,

 

I have a table of telesales logs containing company names and outcomes of the telesales calls. Let's call this table TableLogs. Companies can't always be succesfully contacted on the first try (and can sometimes never be contacted succesfully), so there can be multiple rows per company name. Example:

 

Table name: TableLogs

Company nameResult
Company AInterested
Company BNot able to contact
Company BInterested
Company CNot able to contact
Company CNot able to contact
Company CNot interested
Company DNot able to contact
Company DNot able to contact

 

I also have a table containing the full list of company names (distinct values, each company name appearing only once). Let's call this table TableReport. For each row in this table, I want to look up the company name in TableLogs and get one result based on the following criteria:

- if the only result for a company in TableLogs is "Not able to contact", then show that result in TableReport

- if one of the results for a company in TableLogs is "Interested" or "Not interested", then show that result in TableReport

- if the company doesn't exist in TableLogs, then show the result as "Not contacted yet" (this one is probably fairly trivial)

 

Example of what it should look like based on my TableLogs example:

 

Table name: TableReport

Company nameResult
Company AInterested
Company BInterested
Company CNot interested
Company DNot able to contact
Company ENot contacted yet

 

What I have so far are the two tables TableLogs and TableReport with a 'many-to-one' relationship between them. How do I create the Result column in TableReport with DAX to achieve the outcomes described above? I feel like this shouldn't be too complicated but I am very new to Power BI and have been trying to solve this for hours with no success. I hope a kind soul can help me out here. Smiley Happy

 

Best regards,

 

Micah

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Looking up multiple associated values in related table and filtering conditionally

Does your first table have a Date or Index column that would indicate the latest one? If not, you could probably get there by creating a table of "Interested", then a table of "Not interested" and then a table of "Not able to contact" and then doing EXCEPT and UNION on the tables.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Highlighted
Super User
Super User

Re: Looking up multiple associated values in related table and filtering conditionally

Does your first table have a Date or Index column that would indicate the latest one? If not, you could probably get there by creating a table of "Interested", then a table of "Not interested" and then a table of "Not able to contact" and then doing EXCEPT and UNION on the tables.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MicahV Frequent Visitor
Frequent Visitor

Re: Looking up multiple associated values in related table and filtering conditionally

Hi Greg,

 

Thank you very much for your reply. I will look into your suggestion of creating separate tables and doing EXCEPT and UNION. If it works, I will be sure to mark your post as a solution.

 

My first table does have a Date column that can indicate the latest entry (didn't mention it because I didn't think it would be relevant). Would that make the solution easier?

 

Best regards,

 

Micah