Table Visualization Display All Customers In A State When A Single Slicer Value Selected

[bprokop]

Hi,

I am trying to create a Table that maintains All Customers within the State when a Single Customer is selected in a Slicer.  See example below.  Below I have a SALES and CUSTOMER table and I wish to create the CUSTOMER STATE TABLE that will always display All Customers in the State.  Right now, I can either only show the select Customer Value in the table or I can use Format-->Edit Interaction to show ALL Customers.  Hope this makes sense.  Thanks in advance for any help.

SALES		CUSTOMER
Customer	State	Sales		Customer	Customer Name
1	CA	100000		1	Brainy Smurf
2	CA	200000		2	Papa Smurf
3	CA	300000		3	Smurfette
4	FL	300000		4	Handy Smurf
5	FL	400000		5	Baker Smurf
6	FL	600000		6	Jokey Smurf
7	NY	900000		7	Azriel
8	NY	100000		8	Gargamel
9	TX	800000		9	Vanity Smurf
10	TX	600000		10	Hefty Smurg
Customer Slicer	4
Table Visualization - I would like to create a Table that displays all Customer in the State of the Selected Customer in the Customer Slicer.  Example below:
STATE CUSTOMER TABLE
Customer Name	State	Sales
Handy Smurf	FL	300000
Baker Smurf	FL	400000
Jokey Smurf	FL	600000

[ThxAlot]

SMURF.pbix

Construct the CUSTOMER dimensional table this way,

[bprokop]

I see how your solution works with the sample but I am struggling to integrate it into my actual data.  I have attached a link to the actual pbix used.  In the model, I have a Slicer for "Hospital".  I inserted a Table and tried to recreate the Measure used in your example but was unsuccessful.  Measure equivalents in attached model are: Hospital Name = Name, SALES Qty = State Rank Filter and Rank = SUM Sales.  With these calculation when I use the Slicer to filter by a Hospital, the Table will only display the MAX Hospital in the State.  How do I get ALL Hospitals in a State to display when a single Hospital is selected with the Data Set in the pbix attached.  Thanks for your help.

https://drive.google.com/file/d/16g6ooz88UbtQLdZFQvKkObLIUquuK9zN/view?usp=sharing 

[Ashish_Mathur]

Hi,

Please clarify the result that you want to see.  Let's say that you select Good Samaritan Hospital in the slicer.  What exact result do you want to see - show the result in a table.

[bprokop]

Hi Ashish,

If you select Good Samaritan Hospital and it is located in California, I want the Table to display all Hospitals with the State of California.  Hopefully, that makes sense.  

Brian

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[parry2k]

@bprokop check this video on my channel, addressing a similar question: How to override slicer selection to return all records | PeryTUS - Power BI How To - YouTube