Forum Discussion
Anonymous
7 years agoNot applicable
Counting overlap between two tables
Hello Everyone,
Having trouble getting a calculation result to render in a table and I am hoping someone can point out a glaring mistake I am making.
First a little background about the data I am working with:
- Products are bundled: "Header" to "Component"
- Components can belong to multiple headers
- New headers are created each year with updated components
- Its possible for someone to not own a specific header bundle but still own some of its components
My goal is to look at an customers total components and determine how many belong each header.
I wrote the following measure:
Owned Related Component =
var component_holdings = SELECTCOLUMNS(ALLEXCEPT(d_product_c, d_product_c[access_id]),"ID", VALUES(d_product_c[access_id]))
var pack_components = SELECTCOLUMNS(d_pack_component_products,"ID",VALUES(d_pack_component_products[access_id]))
var overlap = NATURALINNERJOIN(component_holdings,pack_components)
RETURN
COUNTROWS(overlap)My thinking here was to make a variables for:
- all components owned by customer
- all components under any given header (in a table visualization conext)
- all matching values
And then finally count the rows and have a value for all the components a customer owns relative to all components a header product actually has.
The above equation returns an error which I am fairly certain is a result of the inner join.
- Anonymous7 years ago
Anonymous - It's failing on the SELECTCOLUMNS - try this:
Owned Related Component = var component_holdings = SELECTCOLUMNS(d_product_c,"ID", [access_id]) var pack_components = SELECTCOLUMNS(d_pack_component_products,"ID", [access_id]) var overlap = NATURALINNERJOIN(component_holdings,pack_components) RETURN COUNTROWS(overlap)
1 Reply
- AnonymousNot applicable
Anonymous - It's failing on the SELECTCOLUMNS - try this:
Owned Related Component = var component_holdings = SELECTCOLUMNS(d_product_c,"ID", [access_id]) var pack_components = SELECTCOLUMNS(d_pack_component_products,"ID", [access_id]) var overlap = NATURALINNERJOIN(component_holdings,pack_components) RETURN COUNTROWS(overlap)