Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Filter table after drill through using different column values from the passed value

Hi,

 

1. I have a table with three columns: Destination, Block and Shop. I click on a row under the Shop column and then drill through on this value. Example:

 

DestinationBlockShop
Oxford StreetANike
Oxford StreetAApple
Oxford StreetBVodafone
Carnaby StreetAGucci

 

2. On the drill through page, I have another table with the same format. If I drill through using 'Nike', I would like the new table to display the following, i.e. find all of the Blocks where nike is present and return all of those blocks.

 

DestinationBlockShop
Oxford StreetANike
Oxford StreetAApple

 

Any help greatly appreciated as I cannot figure this one out! Thanks 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    In the end, I realised I was overcomplicated this. I created a new table using a full outer join to get all of the combinations of shop matches within a block. I then created a linking table and used a combination of these to put together the table visual and for the drill down filter.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    In the end, I realised I was overcomplicated this. I created a new table using a full outer join to get all of the combinations of shop matches within a block. I then created a linking table and used a combination of these to put together the table visual and for the drill down filter.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    On the destination page, add Block column to Drill-through fields region and switch off Keep all filters. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, this doesn't work because a shop could be in more than one block. Thanks for your attempt though.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I've been trying a new solution. I have generated a new table called groups, which iterates through the original table, adding rows for every possible combination, like so:

     

    DestinationBlockShopBlock Shop CountMatch IDIndex
    Oxford StreetANike211
    Oxford StreetANike222
    Oxford StreetAApple213
    Oxford StreetAApple224
    Oxford StreetBVodafone115
    Carnaby StreetAGucci116

     

    I am then trying to calculate a new column using the below, but I am just getting blanks. The Lookup table is a duplicate of the above.

     

     

     

    Pair = 
LOOKUPVALUE (
    'Groupings Lookup'[Shop],
    'Groupings Lookup'[Destination], 'Groupings'[Destination],
    'Groupings Lookup'[Block], 'Groupings'[Block],
    'Groupings Lookup'[Match ID], 'Groupings'[Match ID],
    'Groupings Lookup'[Index], Groupings[Index] + Groupings[Block Shop Count]
)

     

     

     


    The expected output should be: 

     

     

    DestinationBlockShopBlock Shop CountMatch IDIndexPair
    Oxford StreetANike211Nike
    Oxford StreetANike222Apple
    Oxford StreetAApple213Apple
    Oxford StreetAApple224Nike
    Oxford StreetBVodafone115Vodafone
    Carnaby StreetAGucci116Gucci


    My calculation for the first row would be
    Search for:
    - Destination: Oxford Street
    - Block: A
    - Match ID: 1
    - Index: 1 + 2 = 3
    Which in theory should bring us Apple. Which isn't quite what I was looking for but it is still a correct pairing but as I said, I am just getting blank which suggests a match wasn't found.