Help linking a summary table to a detailed table

[HamidBee]

I am working with two tables. One is sort of the table with all of the main information and details:

Table 1

The second table is sort of a summary of the first:

Table 2

What I am aiming to do is to create a bar chart using Table 2 whereby if I click a particular letter for a particular month it would provide me information relating to that letter and the dates within that month from Table 1. I don't think I can create a relationship for these two tables. Drill down? Tool tip? If that's possible. 

I'd kindly like to ask for the best method to tackling this.

Thank you,

 

[v-yalanwu-msft · Accepted solution]

Hi, @HamidBee ;

You could unpivot table2's ABCD... column ,then create a colum as key, such as :

1.unpivot columns.

2.create a key column.

table1's column:

Column1 = FORMAT([date],"yyyy-mm")&[Letter]

table2's coulmn:

Column2 = [Dates]&[Letter]

 3.create relationship by key column.

4.create a tooltip.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@HamidBee , You can create a common date table and join two both tables. Now if create visual using Date table, it will filter when clicking in one visual it will pass values to another table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[HamidBee]

The problem is that table 2 is a monthly summary. So its a summary of the entire month. Table 1 however gives a detailed breakdown (day, month, year). Is this still possible?