Counting overlap between two tables

[Anonymous]

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:

1. Products are bundled: "Header" to "Component"
2. Components can belong to multiple headers
3. New headers are created each year with updated components
4. 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:

1. all components owned by customer
2. all components under any given header (in a table visualization conext)
3. 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.

[Anonymous · Accepted solution]

@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)