Looking up multiple associated values in related table and filtering conditionally

[Anonymous]

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 name	Result
Company A	Interested
Company B	Not able to contact
Company B	Interested
Company C	Not able to contact
Company C	Not able to contact
Company C	Not interested
Company D	Not able to contact
Company D	Not 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 name	Result
Company A	Interested
Company B	Interested
Company C	Not interested
Company D	Not able to contact
Company E	Not 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. 🙂

Best regards,

Micah

[Greg_Deckler · Accepted solution]

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.

[Anonymous]

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