Using intermediate tables with one column to filter two tables

[Joel-CG]

Hello,

I'm making a Power BI that uses two origins on the same dashboard. To make filters that can be used for both origins, I create a new calculated table with the unique values of some shared columns between the two tables. And then use it to filter. 

This works as intended, however the client wants to do this for like 30 columns, some of them imply merging 4 tables. Is this a bad idea? It will have an important performance impact when refreshing?

Thanks in advance,
Joel

[suparnababu8]

Hi @Joel-CG 

Yes obviously it hit the pbi performance we apply same procedure on many tables.

You need to create seperate dimtable with all relevenat common columns. You can use UNION dax function to create new dimtable

try with  this measure

ProductDim = DISTINCT(UNION(SELECTCOLUMNS(Table1, "Product", Table1[Product]), SELECTCOLUMNS(Table2, "Product", Table2[Product])))

[Joel-CG]

Hi @suparnababu8 , thanks for your reply,

I tried it, and it seems to go faster. Why does this has better performance than using power query?

Now trying it it seems that doing this won't work, since if i try to make the relation many-to-many for both tables it will say that there are duplicates, therefore i can't activate both relations at the same time. I'm missing something? Or i should go with individual dim tables?

Thanks in advance