0 Replies Latest reply on Oct 26, 2018 7:48 AM by S Shah

    Action Filter Multiple Data Sources with Many to Many relations.. Need Help

    S Shah

      Hello fabulous team,

      I am kind of stuck with figuring out solution.

      I have one data source query with quite a lot of records of orders ( even aggregated orders for one year has 7 to 8 dimensions that we want to dissect by ).

      Then each order can have multiple products/offer associated with it ( this is another query having even bigger number of records - One order to Many products ).

      The only link between the two data sources is ORDER_ID

      I had two approaches to take..

      1.  Use one connection and join the queries.

      This would create unnecessary Cartesian joint and would have double counting on Order level data that would again make me use different aggregation to get correct record counts for Order level dimensions.

       

      2. Use two separate connections each with it's own query.

      I liked this approach mainly to keep analysis of order level dimensions separate, simple and clean. I can join the two datasources with ORDER_NO and try to use ACTION FILTER.

      I have gone through community posts and various other videos.

       

      Trouble I have.

      Say.. I have to filter a NJ state orders with 2000 orders for a month, and list all the products sold for those orders in another sheet.

      This I can not achieve..

      I have two sheets.

      Sheet 1 (Order Level Dimensions)  -   State / Location / Rep etc with ORDER counts

      Sheet 2 (Product Level 2 dimensions only )  - Product Names / Order_No with Product Counts  ( one order can have multiple products - Though I don't need to show counts as much as which products )

       

      I created two sheet/ visualization etc.. Put it on dashboard.

      Tried to create action filter. Made sure the "Data -> Edit Relationship" is properly set on each data source and the joined field "ORDER_ID"

      As i have seen in numerous other posts, I need to have "ORDER_ID" on either ROW/COLUMNS/MARKS (DETAIL) in order for it to work properly.

       

      It DOES, but it DEFEATS the purpose ... I can't have each state and then show thousands of marks on the primary Sheet 1 and then click on individual order.
      I just want to allow the click on STATE or any other aggregated dimension and then pass however many ORDER_IDs to the secondary sheet (Sheet 2) via ACTION FILTER to show which PRODUCTS were sold.

       

       

      The purpose is to quickly find out different products sold...

      Also, the volume of data is very large, thus I can't afford to add more dimensions to SHEET 2 so that for each dimension in SHEET1, I can establish a link...

      Please let me know if there is another way...

      Shah