connect tables by customer ID and date

[Sut_Datanaut]

Hey all, 

Hoping to get some help on a problem I am having. I have 4 tables, with 3/4 having customer ID, date and some form of value (revenue, phone calls etc..) each one of these three tables also have a category that extrapolates the value (so the revenue will have application, phone calls with have location etc..)

The other table is a survey table that I am trying to use as the central table which pulls all of the other information from the other tables. The problem is that the survey table has customer ID, response and survey score, but:

1. 1 customer can have multiple survey's and survey dates

2. there does not need to be a customer ID related to a survey, but i would still like to include these in the overall number

I tried to combine account number and date to one column and m2m connect the 3 tables to the 1 survey table,  but since the date on the survey table is only relayed to their survey date, values only connected in the date in which they had a survey, and not by the full account. 

Desired output is below

Example of 3/4 tables (all follow same extrapolation)

Customer ID	Date	Application	Revenue
1	1/1/2023	eBay	$            232
1	1/1/2023	Amazon	$         5,453
1	2/1/2023	eBay	$            546
1	2/1/2023	Amazon	$         7,657
1	3/1/2023	eBay	$            353
1	3/1/2023	Amazon	$       32,423
1	4/1/2023	Amazon	$       32,666
1	5/1/2023	eBay	$            232
1	6/1/2023	eBay	$         5,467

Example of the Survey Table 

Customer ID	Survey Month Date	Survey Score	Survey Comment
1	1/1/2023	4.00	I like this
1	2/1/2023	2.00	its ok
1	4/1/2023	1.00	its bad
2	1/1/2023	4.00	it great
3	3/1/2023	2.00	its ok
(blank)	2/1/2023	4.00	I like this app

Desired output

Customer ID	Month Date	Application	Revenue	Survey Date	Survey Score	Survey Comment
1	1/1/2023	eBay	$            232	1/1/2023	4.00	I like this
1	1/1/2023	Amazon	$         5,453
1	2/1/2023	eBay	$            546	2/1/2023	2.00	its ok
1	2/1/2023	Amazon	$         7,657
1	3/1/2023	eBay	$            353
1	3/1/2023	Amazon	$       32,423
1	4/1/2023	Amazon	$       32,666	4/1/2023	1.00	its bad
1	5/1/2023	eBay	$            232
1	6/1/2023	eBay	$         5,467

The problem I am ultimatley running into is that I need this kind of view across all 3 value based fact tables WITH their corresponding category without duplicating the values. 

Any help is appreciated. 

[Anonymous · Accepted solution]

HI @Sut_Datanaut,

I check the sample data and not found any field can be used to accurate mapping the comment and score from survey table to correspond applications.
Did you mean these survey data show display on all related rows that recorded with the same customer and date values?

If that's the case, you can create two calcuated columns to the first table with lookupvalue function to get values based on current customer id and date values.

Score =
LOOKUPVALUE (
    Survey[Score],
    '3/4'[Customer ID], Survey[Customer ID],
    '3/4'[Date], Survey[Date],
    BLANK ()
)

Comment =
LOOKUPVALUE (
    Survey[Comment],
    '3/4'[Customer ID], Survey[Customer ID],
    '3/4'[Date], Survey[Date],
    BLANK ()
)

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng

[Sut_Datanaut]

How does this compare to using userelationship within a calculated measure? I see the logic looks somewhat similar. Also can you explain the use of 3/4?

Thanks!!