Forum Discussion
Filter Field Parameter by Field Parameter Values without Calculated Groups
Hi,
I have a Field Parameter set up in my report, as follows:
P: Fleet or Route = {
("Fleet Groups", NAMEOF('D: Aircraft'[Fleet Groups]), 0),
("Route Group", NAMEOF('M: Market Segmentation'[Route Group]), 1),
("Route Segment", NAMEOF('M: Market Segmentation'[Route Segment]), 2)
}I'm using this on a report page with multiple visuals and metrics, to allow users to switch the axis. This part is fine.
What I want to do is add a hierarchy slicer to the page that contains a list of the 3 Fields from the Field Parameter. I've created a disconnected table to do this, as following:
P: Fleet or Route Hierarchy =
UNION (
SELECTCOLUMNS (
DISTINCT('D: Aircraft'[Fleet Groups]),
"Fleet or Route", "Fleet Group",
"Field Detail", 'D: Aircraft'[Fleet Groups],
"Sort Order", 1
),
SELECTCOLUMNS (
DISTINCT('M: Market Segmentation'[Route Group]),
"Fleet or Route", "Route Group",
"Field Detail", 'M: Market Segmentation'[Route Group],
"Sort Order", 2
),
SELECTCOLUMNS (
DISTINCT('M: Market Segmentation'[Route Segment]),
"Fleet or Route", "Route Segment",
"Field Detail", 'M: Market Segmentation'[Route Segment],
"Sort Order", 3
)
)I want to allow users to choose a Field Parameter for their table, then use the Hierarchy Slicer to narrow down results. For example:
- User selects Fleet Groups from Field Parameters
- In the Hierarchy Slicer the users chooses one of the Fleet Groups and also chooses a Route Group. The result would be that the axis filters to the Fleet Group and the value filters to the Route Group field
- All these tables are linked to each other via many to one relationship to a Fact table
Is there a way to add a filter to the visual that would allow this to happen? I don't want to have to create duplicate wrapped measures just to do this.
Any ideas?
Without creating an extra measure, your current hierarchy filter won’t fully work. You’ll be able to filter Fleet Group, but only one of Route Segment or Route Group will work at a time since they come from the same table and only one relationship can be active.
If you want to avoid using a measure, your options are to create separate dimension tables for Route Segment and Route Group, or add a column in your hierarchy table that uniquely identifies each row in the fact table, like an Index or Record ID. That second option can get messy, especially if you have multiple fact tables.
Either way, even with your current hierarchy table, you’ll likely need to deal with a many-to-many relationship.
Try the attached pbix.
6 Replies
Hi MJ_Holland
If my understanding is correct, you want to use field parameters to change the field in the visual, and a hierarchy table to filter the selected field independently of the field parameters. Why not just use two field parameters—one for switching dimensions and another for filtering?
Your current approach requires two relationships to the Market Segmentation table - Route Segment and Route Group - with one being inactive - and you’ll end up needing a measure to activate the inactive one
Create another field parameter like you normally would. This will be independent from the first parameter but you can use this within the same visual and even if you don't you can stil use it to filter the value of that visual.
Hi MJ_Holland,
Thank you for reaching out to Microsoft Fabric Community.
Thank you danextian for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
