Filtering table based on parent value of selected slicer value

[hass18]

Hi, i've searched far and wide for this but cannot find any solution. I have data as below. Ultimately what i want to do is when i select a Store i.e. Store B in a slicer. I would like to filter my visualisation on the corresponding region to that slicer value which would be R1. In that visualisation i would then expect to see Store A, B and C represented. At the moment when i select store B in then my visualisiation will show only that store.

Store	Region
A	R1
B	R1
C	R1
D	R2
E	R2
F	R2

many thanks in advance for your help!

[Arul · Accepted solution]

@hass18 ,

First you have to create separate disconnected table for slicers (store), then use the below code in the measure,

Region = 
 VAR _selectedStore = SELECTEDVALUE('DisconnectedTable'[Store])
 VAR _tempTable = CALCULATE(
     MAX(
         'OriginalTable'[Region]),
         'OriginalTable'[Store] = _selectedStore,ALL('OriginalTable'))
RETURN IF(MAX('OriginalTable'[Region]) = _tempTable, MAX('OriginalTable'[Region]),BLANK())

use the above disconnected table column in the slicer and measure in the table visual.

[hass18]

Hi thanks so much for responding. I did exactly as you said but it doesn't seem to be working. I created a disconnected table using the below and used the measure as a column in the table but it seems to only return values from the max region even (in this case R23) even though no store is selected that belongs to R23

StoreList = Summarize(OriginalTable,OriginalTable[Store])

[Arul]

@hass18 ,

In my testing file it is showing blank if you are not selecting anything in the slicer. Can you refer the file attahced?

[hass18]

Sorry completely my fault, i noticed a made an incorrect reference in the code you supplied. Its now working perfectly. You are amazing! thankyou so much

[DataVitalizer]

Hi @hass18 

To make sure I undersood your request, are you trying to exclude the selected value in the slicer from the visual?

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

[hass18]

i'm trying to only show stores in the visualisation that belong to the region of the selected store. i.e. User selects Store B > that belongs to R1 > therefore the visualisation should show Stores A, B, C