topic Re: Need a help with applying filter from table that are connect through bridge table. in DAX Commands and Tips

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

Anonymous — Sat, 20 Nov 2021 01:00:07 GMT

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

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

VahidDM — Sat, 20 Nov 2021 02:23:22 GMT

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/

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

Nathaniel_C — Sat, 20 Nov 2021 03:04:29 GMT

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

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

Anonymous — Sat, 20 Nov 2021 15:50:24 GMT

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

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

Nathaniel_C — Sat, 20 Nov 2021 17:20:27 GMT

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

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

Anonymous — Sat, 20 Nov 2021 17:51:49 GMT

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

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

Nathaniel_C — Sat, 20 Nov 2021 18:23:27 GMT

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

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

Anonymous — Sun, 21 Nov 2021 04:12:53 GMT

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

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

Anonymous — Wed, 24 Nov 2021 05:53:39 GMT

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])

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