Need a help with applying filter from table that are connect through bridge table.

[Anonymous]

Hello,

Can you please help me with following querry:

I have two tables Transactional and Email Activity table.  Both has many to many relation based on compnay id.  To avoid many to relation, I created bridge table with unique compnay ids.  My main table (fact table) is Email Activity table. I want to create filter where I could filter email activities based on product type. 

I want to use date from Email activity table. Like how many email were sent and how many they purchased the products.

I want to use single directional filter to avoid any issues.

I am ok with both measure or column.  I want to use date from Email activity table.

Thankyou so much for any give help

Transactional Table:

Transaction Data
Company ID	Dates	Product type
1	1/15/2020	AB
1	1/16/2020	AC
1	1/17/2020	DE
2	1/20/2020	FX
2	1/20/2020	EX
3	1/15/2020	FX
10	1/16/2020	FX
10	1/17/2020	FXG
11	1/18/2021	AB
11	1/19/2021	AC
5	2/30/2021	AD
1	2/3/2021	AD

Email Activity:

Email Activity
Date	Contact Email  address	CompanyID
2020-JAN-1	A	1
2020-JAN-1	B	1
2020-JAN-2	B	1
2020-JAN-3	C	2
2020-JAN-4	D	3
2020-JAN-5	E	12
2020-JAN-6	F	11
2020-JAN-7	G	10
2021-JAN-1	X	5
2021-JAN-1	F	7
2021-JAN-2	H	8
2021-JAN-3	L	8
2021-JAN-4	I	9
2021-JAN-5	I	9
2021-JAN-6	E	12
2021-JAN-7	A	1

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Year_measure =
var _select=SELECTEDVALUE('Email Activity'[CompanyID])
return
MAXX(FILTER('Transactional Table','Transactional Table'[Company ID]=_select),[year])

Year_measure =
var _select=SELECTEDVALUE('Email Activity'[CompanyID])
return
MAXX(FILTER('Transactional Table','Transactional Table'[Company ID]=_select),[year])

2. Result:

Best Regards,

Liu Yang

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

[Nathaniel_C]

Hi @Anonymous , sorry for the misunderstanding, but it is still not clear what you are after.  For example Co1 sent 3 emails in 2020, right? and 1 in 2021. Then Co1 purchased 3 products in 2020 - AB, AC, DE - and 1 product in 2021 - AD-.  However your result table does not reflect that.
Thank you,

Nathaniel

[Anonymous]

@Nathaniel_C  My apologies, Yes you are very right.  I need to create a filter of product type on the Email activity table which can filter a number of emails send. Just like in this case, If I have to apply filter on  Product AB then we it will give an output of 3 for 2020 year.

I appreciate your time in helping me into it.

Thank you so much!

Company ID	Product Type	Email Sent	Year
1	AB	3	2020
1	AC	3	2020
1	DE	3	2020
1	AD	1	2021

[Nathaniel_C]

Hi @Anonymous , I am sorry, but where does TypeEmail come from?
Nathaniel

[Anonymous]

@Nathaniel_C 

Sorry, table was not clear earlier.  Product type and Email send are two seperate columns. Email send is coming from email activity ( i.e it is main  table) and Product type is coming from transactional date. 

Thankyou so much

Company ID	Product Type	Email Send	Year
1	AB	1	2020
1	AB	1	2021
1	AC	1	2020
2	FX	1	2020
2	EX	1	2020

[Nathaniel_C]

Hi @Anonymous @VahidDM ,

Would you please give us what your expected outcome would look like?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Hi @Nathaniel_C :

I need resutls  some thing like this : 

I want to know  how many email we sent and what product they have purchased.  I have to make the relation bewteen above table based on compnay IDs.  Any suggestions are welcome!

Thankyou so much !

Company ID	Product Type	Email Send	Year
1	AB	1	2020
1	AB	1	2021
1	AC	1	2020
2	FX	1	2020
2	EX	1	2020

[VahidDM]

Hi @Anonymous 

You need to add a Calendar Table (Date Table) to your model and connect those 2 tables with that.

How to create a Calendat Table : https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

Then you can use date from Calendar table to find the Related Emails and Peoducts.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/