Select a value in a column, then filter a the table by the value of another column in the same row

[skingi20]

I have a dataset called address_station:

address	station
rocky road 1, New York	100
rocky road 2, New York	100
rocky road 3, New York	100
streetway 1, Manchester	200
streetway 2, Manchester	200
streetway 3, Manchester	200
other road 1, Germany	300
other road 1, Germany	400
happy road 1, Germany	300
happy road 2, Germany	300
happy road 3, Germany	300
sad road 1, Germany	400
sad road 2, Germany	400
sad road 3, Germany	400

I use a slicer on this dataset to select 1 and only 1 address. For instance I select rocky road 1, New York.
I then want to return a table, that is filtered on the data, such that all of the rows which has the same station as rocky road 1, New York, gets returned. Like this:

address	station
rocky road 1, New York	100
rocky road 2, New York	100
rocky road 3, New York	100

This is the main problem and what I mainly want to do. Though if possible I also have some special cases I would like to handle. For instance as can be seen "other road 1, Germany" appears twice with different stations. If this road is selected I want this returned. i.e. all the stations associated with that address.

other road 1, Germany	300
other road 1, Germany	400
happy road 1, Germany	300
happy road 2, Germany	300
happy road 3, Germany	300
sad road 1, Germany	400
sad road 2, Germany	400
sad road 3, Germany	400

I have tried using DAX meausures and DAX tables.
Measure 1:

SelectedAddress =

SELECTEDVALUE('address_station'[Address])

Measure 2:

SelectedStation =

VAR SelectedAdressValue = [SelectedAddress]

RETURN

CALCULATE(

    SELECTEDVALUE('address_station'[Station]),

    'address_station'[Address] = SelectedAdressValue

)

Measure table:

FilteredAdresses =

VAR SelectedStationValue = [SelectedStation]

RETURN

    CALCULATETABLE(

        'address_station',

        'address_station'[Station] = SelectedStationValue

        )


The two measures work fine and returns the expected value, but the measure table returns the same thing, no matter the selection.

[skingi20 · Accepted solution]

I found the solution after your hints and some more googling. Apparently I had to remove the duplicates in Address to get the right results, since it does not work to return multiple stations I think. So this problem is still unsolved, if there is multiple stations for each address. Or more generally multiple adults to the same child.
Well on to the solution:

I found this thread which was quite helpful:
https://community.fabric.microsoft.com/t5/Desktop/Filtering-a-parent-based-on-the-child/td-p/2317956

And in specifics how I did it:
First I created a calculated table like this:

Slice_Table = DISTINCT('address_station'[address])

Then this measure:

Flag =

var _addressselect=SELECTEDVALUE('Slice_Table'[address])

var _station=CALCULATE(MAX('address_station'[station]),FILTER(ALL(address_station),'address_station'[address]=_addressselect))

return

IF(

    MAX('address_station'[station])=_station,1,0)

Put the Slice_Table address in the slicer:

And putting the flag = 1 in the visual:

[Samarth_18]

Hi @skingi20 ,

You could try the following:-

FilteredAddressesTable =
VAR SelectedStationValue = [SelectedStation]
RETURN
ADDCOLUMNS(
    FILTER(
        'address_station',
        'address_station'[station] = SelectedStationValue
    ),
    "Address", 'address_station'[address],
    "Station", 'address_station'[station]
)

[skingi20]

As the code is there it does not work when creating a dax table. I also have tried to change a few things in it to fix the errors, but it still does not give the intended result. Could you clarify further what this is or is supposed to be?

[Samarth_18]

@skingi20 , To answer specifically could you please share the expected output sample?

[skingi20]

The expected output sample is in my original post? Is there any more information you need?

[amitchandak]

@skingi20 , A calculated table cannot use measure values to filter. Tables are static in nature and are loaded at data load, so any change in slicer will not change the values.

[skingi20]

Arh okay, many thanks! Is there then another solution to this problem, which does not involve filtering in a table?