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

[jameslloyd_tl]

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:

Destination	Block	Shop
Oxford Street	A	Nike
Oxford Street	A	Apple
Oxford Street	B	Vodafone
Carnaby Street	A	Gucci

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.

Destination	Block	Shop
Oxford Street	A	Nike
Oxford Street	A	Apple

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

[jameslloyd_tl · Accepted solution]

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.

[jameslloyd_tl]

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:

Destination	Block	Shop	Block Shop Count	Match ID	Index
Oxford Street	A	Nike	2	1	1
Oxford Street	A	Nike	2	2	2
Oxford Street	A	Apple	2	1	3
Oxford Street	A	Apple	2	2	4
Oxford Street	B	Vodafone	1	1	5
Carnaby Street	A	Gucci	1	1	6

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: 

Destination	Block	Shop	Block Shop Count	Match ID	Index	Pair
Oxford Street	A	Nike	2	1	1	Nike
Oxford Street	A	Nike	2	2	2	Apple
Oxford Street	A	Apple	2	1	3	Apple
Oxford Street	A	Apple	2	2	4	Nike
Oxford Street	B	Vodafone	1	1	5	Vodafone
Carnaby Street	A	Gucci	1	1	6	Gucci

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.

[v-jingzhan-msft]

Hi @jameslloyd_tl 

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!

[jameslloyd_tl]

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