Look up a value based on three matching conditions with another table

[Anonymous]

I have two tables: 

table 1 has client name, purchase date, product, sales rep

table 2 has client name, contact date, sales rep

The relationship is built through a factor table of "client name". Other factor tables are "Date "table, "Sales Rep" table.

I tried to retrieve the information that shows the client names who have made purchases at a time period (controlled by the Date slicer) and whether these clients are contacted by their designated sales reps in the past year.  The information needs to be filtered  by a date slicer and a sales rep slicer.  I've tried "Calculatetable (table 1, keepfilters(table 1[client name] in values(table2[client name]), keepfilters(values(table1[sales rep])). This wouldn't work itself. I also don't know how to expand the search time period to a year from today and not impacted by the Date slicer. Can anyone please help? Thanks so much!

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-eqin-msft]

Hi @Anonymous ,

Based on this: I also don't know how to expand the search time period to a year from today and not impacted by the Date slicer. 

It looks like you need a Date table for slicer that has no relationship to the other tables. For example:

Date period of last year = 
IF(MAX('Table'[Date])  >= MIN('Dates for slicer'[Date])-365 && MAX('Table'[Date])<= MAX('Dates for slicer'[Date])-365,1,0)

So as @lbendlin suggested, please provide me with more details about your table and your problem to help us clarify your scenario. Or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523