Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
Solved! Go to 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
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
Thank you!
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |