Forum Discussion
Correlation in Product Structure
Hello there, I'm working on a model where I could look at the similarity between products through the product structure. The product structure looks like this: TopProduct ComponentPart ...
Anonymous - I did it like this:
Measure 2 = VAR __T = MAX('Table (4)'[TopProduct]) VAR __T1 = MAX('Table 2'[TopProduct]) VAR __Parts = SELECTCOLUMNS(FILTER(ALL('Table (4)'),[TopProduct] = __T),"__Parts",[ComponentPart]) VAR __Parts1 = SELECTCOLUMNS(FILTER(ALL('Table (4)'),[TopProduct] = __T1),"__Parts",[ComponentPart]) VAR __In = INTERSECT(__Parts,__Parts1) RETURN COUNTROWS(__In) / COUNTROWS(__Parts)PBIX is attached below sig, you want Table (4) and Table 2.
Amazing! Thank you!
Need to understand the dax-code behind it still, but that did the trick! Thanks alot!
Anonymous - Sure, the first two lines simply figure out where we are in the matrix getting the value in the row and the column. We then create two table variables that filter out the rows in our base fact table based upon the values we have for our row and column. We use SELECTCOLUMNS to just get the column we are interested in.
INTERSECT is the key, it returns the rows that are common between the first table and the last table. So then it is just dividing this by our count of rows.