Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Solved! Go to 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:
And putting the flag = 1 in the visual:
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:
And putting the flag = 1 in the visual:
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]
)
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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?
The expected output sample is in my original post? Is there any more information you need?
@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.
Arh okay, many thanks! Is there then another solution to this problem, which does not involve filtering in a table?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |