Filter Field Parameter by Field Parameter Values without Calculated Groups

[MJ_Holland]

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?

[v-achippa]

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

[danextian]

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

[MJ_Holland]

Hi @danextian ,

How would the second Field Parameter be set up and applied to the visuals?

[danextian]

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. 

[MJ_Holland]

Hi @danextian ,

This isn't quite what I'm trying to achieve. This only allows me to filter by the list of Fields from the selected Field Parameter. But in my example, I need the user to be able to filter fields from all 3 Fields parameters, even when it's not the one that's been selected.

For example:

• my Field Parameter has 3 options - Fleet Group, Route Group and Route Segment
• Fleet Group will have different aircraft types while Route Group will have different route options, like Asia or America
• If one of my users have Fleet Group selected from the Field Parameter, the axis will show a list of different Aircraft Types
• With this additional filter I want the user to be able to filter by Route Group, say America, and all the Aircraft Types from Fleet Groups will narrow down while the axis remains the same
• Similarly, if someone selects A380 from Fleet Group and America from Route Group, the axis, which is showing the Field Parameter Fleet Group, show filter to A380 while the value in the chart would narrow down to just America
• So I need the axis to work with the Field Parameter and to be able to be filtered by the Hierarchy filter as well as for the value to be influenced by the Hierarchy filter

How can I do this?

[danextian · Accepted solution]

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.