How to make my code more flexible with other selections

[Anonymous]

Hi, 

I have the logic below which a fellow community member kindly helped me with and for the initial request, it worked as I needed it to. However, it's quite a static bit of code in isolation and wonder if anyone knew how I could make this more flexible so that if I could join to another table, the volumes would update based on different selections? 

For example, if I want to see the volumes by Comp[Comp_Type] or and add in another table to see it split by Business_Area[Area], any idea how to amend the code to allow this please? 

Year_Volumes = ADDCOLUMNS(ADDCOLUMNS(DISTINCT(UNION(
SELECTCOLUMNS(VALUES(Mort[Mort_Year]),"Year",Mort[Mort_Year]),

SELECTCOLUMNS(VALUES(Comp[Comp_year]),"Year",Comp[Comp_year]))),

"Mort_vol",SUMX(FILTER(Mort,Mort[Mort_Year]=EARLIER([Year])),Mort[Mort_Count])),

"Comp_vol",SUMX(FILTER(Comp,Comp[Comp_year]=EARLIER([Year])),Comp[Mort_Count]))

[amitchandak]

@Anonymous , seem like you are creating a new table, that will not tale slicer values

Create a common dimension year, join with both tables. And create measures from both tables and analyze against common dimension

[Anonymous]

I have tried to do this so now have a table called 'Year_Table' with variable 'Year'.

However, I have joined this table 1 to many from (Year_table[Year] to MORT[Mort_Year] which is fine. However, I then tried to join (Year_table[Year] to COMP[Comp_Year] but it won't let me due to the following message:

"an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter to "Single", delete, or deactivate any indirect relationship first"

Any idea how to bypass this please?