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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

Best regards,

 

Micah

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.