Creating a new column according to reference table

[Curious_Curious]

Hello,

I was wondering if someone would be able to help with something I am stuck on. I have the following table (ProductDate) with information in the format below:

Product number	Date	Day of week
5JR38	16 September 2022	Friday
5JR38	17 September 2022	Saturday
5JR38	18 September 2022	Sunday
7QP13	12 September 2022	Monday
7QP13	13 September 2022	Tuesday
7QP13	14 September 2022	Wednesday
7QP13	15 September 2022	Thursday
7QP13	16 September 2022	Friday
7QP13	17 September 2022	Saturday
7QP13	18 September 2022	Sunday

I also have another table (ProductStock) with the number of stock available for a specific day of the week range:

Product Number	Day ref	Stock
5JR38	FriO	20
5JR38	WEnd	65
7QP13	MFriO	7
7QP13	MidWeek	13
7QP13	WEnd	18

The Day ref column in the table above refers to a range of dates which are stored in a separate table (DayReference) as follows:

Day ref	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Daily	Y	Y	Y	Y	Y	Y	Y
FriO	N	N	N	N	Y	N	N
WD	Y	Y	Y	Y	Y	N	N
MFriO	Y	N	N	N	Y	N	N
WEnd	N	N	N	N	N	Y	Y
MidWeek	N	Y	Y	Y	N	N	N

The 'Day ref' column in the ProductStock table does not overlap (i.e. for a 'Product Number' there is no 'Day ref' which would refer to a day of the week twice).

What I am trying to do is create a new column using DAX in the first table (ProductDate) with the 'Day ref' populated from the ProductStock table only for the days of the week where there is 'Y' in the DayReference table. The expected result should be something like this:

Product number	Date	Day of week	Relevant DayRef
5JR38	16 September 2022	Friday	FriO
5JR38	17 September 2022	Saturday	WEnd
5JR38	18 September 2022	Sunday	WEnd
7QP13	12 September 2022	Monday	MFriO
7QP13	13 September 2022	Tuesday	MidWeek
7QP13	14 September 2022	Wednesday	MidWeek
7QP13	15 September 2022	Thursday	MidWeek
7QP13	16 September 2022	Friday	MFriO
7QP13	17 September 2022	Saturday	WEnd
7QP13	18 September 2022	Sunday	WEnd

Thank you

[Syndicate_Admin · Accepted solution]

Hello, here you have two options:

1. Generate a DIM table (dimension) in which you bring a single value of the products (DISTINC function)
2. Generate a relationship "many to many" (this is the simplest but remember all relationship 'many to many' or "one to one" are weak and can bring several unwanted results)

Best regards

[Syndicate_Admin]

Hello how are you?, I understand what you want to do, but the easiest way is simply to generate a relationship between these tabalas and you can create the table within the dashboard without problem

[Curious_Curious]

Thank you for your response. I was considering this but I am not really sure how to create a proper working relationship between those tables as the product number appears many times in the first two tables.