Power BI | Slicer Filter | Summary Table generation

[Viktor001]

I have a requirement in which I have transformed data in a Table which has say 10 rows. Another table has same columns but has some different data say another 10 columns. Both these tables have a row "total" which displays aggregated data at the end. I want to build another table in the report which gives the total from both these tables. But I am not able to link these tables with any relationship. So slicer filter visualization applies the filter on individual tables but does not reflect changes on summary table. Can you suggest how to acheive filtering throughout the report in this scenario? Or any other approach would also be appreciated. FYI I am preprocessing data from raw JSON. Below given is an example for the same. 

Table 1:-

ID	Type	Value1	Value2
1	A	10	15
2	A	10	15
3	A	10	15
		30	45

Table 2:-

ID	Type	Value1	Value2
1	B	25	20
2	B	25	20
3	B	25	20
		75	60

Summary:-

	A	B	Total
Value1	30	75	105
Value2	45	60	105

Column "ID" is used to apply filter using slicer visualization. In this case when we change ID in slicer filter, table 1 and 2 reflects changes but summary table doesn't as they don't have any relation. How to link them in power BI or any other approach to acheive the same?

[jdbuchanan71 · Accepted solution]

@Viktor001 

You need to create dimension tables that hold the unique values for ID and Type that you link to your fact tables, then you can use measures to summarize your data.  You can use DAX to create the tables:

IDs = 
DISTINCT(
        UNION(
            DISTINCT('Table 1'[ID]),
            DISTINCT('Table 2'[ID])
    )
)

Types = 
DISTINCT(
        UNION(
            DISTINCT('Table 1'[Type]),
            DISTINCT('Table 2'[Type])
    )
)

Then create the relationships like this.

Then some measure to do the sums and add the type (from your types table) to a visual.  In my sample I put the measures on the IDs table but you can put them on whatever table you want.

I have attached my sample file for you to look at.

[Viktor001]

Thanks a lot for the solution. It works fine. But can you suggest how to do the same if we only have 1 table by merging Table 1 and Table 2. And we want to achieve the same goal of summarizing Data based on Type.