Filtering Data

[Chris_68]

Hi everyone,

I'm reaching out for help with a Power BI setup I can't seem to resolve.

The scenario is as follows:

• After selecting the desired TopN value, Table Visual 1 displays customers and their total sales.
  
  

• Upon selecting a customer from Table Visual 1, Table Visual 2 shows the items purchased by that customer.
  
  

• When I then select an item from Table Visual 2, Table Visual 3 is meant to show the total quantity purchased for that item by the selected customer. In this case it should be only 5414 as shown in the above image.

However, Table Visual 3 is currently showing the total quantity purchased by all customers for the selected item, instead of filtering to just the selected customer.

Example: Customer A1172 is selected in Table 1, and item SP12508 is selected in Table 2. Table 3 is showing a quantity of 8,017, but it should only show 5,414, which is the amount purchased by customer A1172.

I'd appreciate any guidance or suggestions from the community on how to correctly filter Table 3 to reflect only the selected customer’s quantity for the selected item.

Thanks in advance!

Link to the Power BI file.

https://1drv.ms/u/c/395f6dba7fbed437/EXA7eIPIqNNAgsno9KL2eg0B93aBukjDjsuQtFvES_gVBA?e=YBHCaw

[OwenAuger · Accepted solution]

To retain selections across multiple visuals, hold down Ctrl when making each selection after the first.

In your example,

1. Table 1: Click A1172
2. Table 2: Ctrl-click SP12508
3. Table 3: Now filtered by Customer Name = A1172 & Item = SP12508

[Chris_68]

Hi Owen,

Glad to confirm it's working.

Is there a way to enable the function without requiring users to hold the Ctrl key? The file will be used by multiple people, and it's likely some may miss that step.

Appreciate your advice once again.

[OwenAuger · Accepted solution]

@Chris_68 Glad to hear it 🙂
As far as I know, selections on any non-slicer visuals (including table/matrix) are always cleared when making another selection without Ctrl.

I believe the only solution would be to resort to slicer visuals of some sort. I mocked up an example of how it might work with Button slicers, just as an idea (attached).

You can play around with formatting, but I would prefer it if the values shown on the buttons could be placed to the right but don't think it's possible without concatenating the values via a measure.

BTW - I adjusted/added a few measures along the way, and some visual-level filters were required for the slicers.

Not sure if this would be suitable for you but might be worth trying 🙂

[Chris_68]

Hi Owen, 

This is fantastic, thank you very much.

Have a great weekend!

Chris

[Chris_68]

Hi Owen,

Noticed that there is a measure Rank Owen 1, is this in use?

Just another question, on the 2nd table visual, is it possible not change the item when making any selection? For example, when selecting SP12508, the items that were listed earlier will be changed. 

Thank you.

Chris

[OwenAuger]

Hi again Chris,

Glad it's useful!

No, Rank Owen 1 isn't being used. I think I tested something out with that measure but didn't end up using it.

On the 2nd question, did you mean you don't want the 2nd table/slicer to filter the 1st table/slicer? On the Slicers page I created, I edited interactions so that the 2nd slicer doesn't filter the 1st slicer.

https://learn.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions?tabs=p...

Let me know if you meant something else 🙂

[OwenAuger · Accepted solution]

To retain selections across multiple visuals, hold down Ctrl when making each selection after the first.

In your example,

1. Table 1: Click A1172
2. Table 2: Ctrl-click SP12508
3. Table 3: Now filtered by Customer Name = A1172 & Item = SP12508