Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:

  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's avatar
    Anonymous
    7 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

  • Anonymous's avatar
    Anonymous
    Not 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)