How to operate with unlinked tables with the same column

[Anonymous]

Hi everyone!

I am working in a project and I would like to multiply the number of elements within a category of two unlinked tables (which have the same categories). I show some dummy data I've just created to make it more visual. 

  Table 1

 Table 2

My goal is representing a colum chart that has 4 columns, each one representing the product of the number of elements of each category of each table, The next image shows, what I want.

My problem is that I've made this by creating two new tables, and I can't do that in my real project since I am using an online database by 'Analysis Services' -> 'Connect Live' so I can't create auxiliary tables/columns and modify relationships (in the original data, Category column can't be related between Table 1 and Table 2).

Do you know how to make this by just using measures? I leave you my dummy data.

https://drive.google.com/drive/folders/1otPwWGRprP-MkC-ZKMptyEYfHkF37r8D?usp=sharing

Thank you!!

[lbendlin · Accepted solution]

At a very minimum you  need to have a reference table with all possible categories.  That cannot be dynamic. 

Then you can add a measure:

V1xV2 = 
var a = values(Categories[Cat])
var b = ADDCOLUMNS(a,"SM", var c=[Cat] return sumx(filter(Table1,[C1]=c),Table1[V1])*sumx(filter(Table2,[C1]=c),[V2]))
return sumx(b,[SM])

And then use that in your visual

[Anonymous]

Thank you!