How to create a visual that populates data depending on selection (different tables)?

[Anonymous]

I am having trouble even writing this post, but I hope you can help me figure out a solution. 

I have around 20 tables that represent Email send outs. Each has 5 columns "Client ID" (Letters and numbers), "Received" , "open" , "click" (all 3 have a number: 1 or higher, clients that did not received it are not part of the table) And "last open" (Date).

• I have an additional table ("Master") with all the client IDs and more details about them.
• I have an extra table with the list of all 20 emails and date of send out.
• The 20 emails belong to 2 different categories. I would like to be able to calculate totals based on single email, by category, time and client. 

What is the best way to create some visuals -

Table 1- Columns Received, Open, click - I want to use a slicer to populate the table according to the selection.

Table 2 -I want to have a client column and show how many emails they have opened in a certain period (Time slicer)

Thank you so much for your help!

[Anonymous]

great answer. May I ask for further instructions?

Point 3. Slicer for Email Selection. My Email list has each Email as a row, how can I create the relation with each table? I am thiking I could add an extra column with the Name of the Email. 

If I do include an extra column, that makes merging different tables harder, right? 

Now, I am thinking it is better to add each action as a row, is it?

Email list

My initial instinct to relate these 2 tables. To add a column with the Email name

My plan A to reduce the number of tables - A wider table, one column per action, one row per client

My plan B, a longer table, One line per Newsletter, Multiple lines per Client ID, should I add the date to this table or leave it in the Email list table?

Thanks for your great guidance

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

For Table 1: Aggregating Received, Open, Click by Selection

1. Create Relationships: Ensure that all your email tables are related to the "Master" table using the "Client ID" column. This will allow you to aggregate data across all email tables based on client details. If not already done, you can follow the steps outlined here to create these relationships.

2. Use Measures for Aggregation: Create measures to calculate the total "Received", "Open", and "Click". Here's an example of how to create a measure for "Total Received":

Total Received = SUMX(RELATEDTABLE('EmailTable'), 'EmailTable'[Received])

Repeat this for "Open" and "Click" across all your email tables.

3. Slicer for Email Selection: To filter by email, ensure your email list table is related to each email send-out table. Use this table to create a slicer. This slicer will allow users to select an email, and the table will dynamically show totals for "Received", "Open", and "Click" for the selected email.

For Table 2: Showing Emails Opened by Client in a Certain Period

1. Time Intelligence: First, ensure your "Master" table has a date column that can be used for time slicing. If not, you might need to create a separate Date table and relate it to your email tables. 

2. Time Slicer: Use the Date table to create a slicer for the period selection. This slicer will control the period for which the data is aggregated.

3. Calculate Opened Emails: Create a measure to calculate the number of emails opened by each client within the selected period. Here's an example measure:

Emails Opened = CALCULATE(COUNTROWS('EmailTable'), 'EmailTable'[Open] > 0)

This measure counts the number of rows where "Open" is greater than 0, effectively counting opened emails.

4. Visual Creation: Create a table visual with "Client ID" from the "Master" table and the "Emails Opened" measure. The time slicer will dynamically adjust the count based on the selected period.

Note: With 20 tables, performance might be a concern. Consider combining similar tables if possible, or using calculated tables to simplify your model.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.