How can I create a slicer that contains unique values from one column based on values in another

[Howeitzer]

I'm reading 9 tables from a SQL Server database, each of which has two common fields; 'PIPELINE_FULL_NAME', and 'SURVEY_FULL_NAME'. I'd like to make a dashboard of some kind where a user can select a table name from a dropdown, then to be able to select from a unique list of pipeline name values that are found in the selected table, then one final drop down selector where the user can select a unique survey from the selected table but only surveys where the pipeline matches the selected one. So far I have one slicer parameter that lists the table names:

Pipeline Switcher = {
("Long Profile", NAMEOF('IPMS GIS_USER_LONGPROF'[PIPELINE_FULL_NAME]), 0),
("KP", NAMEOF('IPMS KP_REPORT_ALL'[PIPELINE_FULL_NAME]), 1),
etc
}

This works great. I created a slicer that lists the actual unique values in the 'PIPELINE_FULL_NAME' field from the table selected in the first slicer. Also works fine.

What I'm struggling with is to create a third slicer that only lists the unique values from the 'SURVEY_FULL_NAME' field in the chosen table for the selected pipeline in the second slicer.

[v-achippa]

Hi @Howeitzer,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Nasif_Azam, @luisoliveira89, @SEMattis and @AnkitKukreja 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

[Howeitzer · Accepted solution]

So I sorted this. I created a unified table that combined all of my tables, added a new column called SourceTable and added relevant values. Created three slicers that referenced the SourceTable, PIPELINE_FULL_NAME, and SURVEY_FULL_NAME columns. This allows users to select what they wish to see. Then I created a DynamicColumns parameter that references each column in from each source table from the UnifiedTable. For example,

DynamicColumns = {

    -- Long Profile

    ("LONGPROF_SURVEY", NAMEOF(UnifiedTable[LONGPROF_SURVEY]), 0, "Long Profile"),

    ("LONGPROF_PIPELINE", NAMEOF(UnifiedTable[LONGPROF_PIPELINE]), 1, "Long Profile"),

    ("LONGPROF_UTME", NAMEOF(UnifiedTable[LONGPROF_UTME]), 2, "Long Profile"),

    ("LONGPROF_UTMN", NAMEOF(UnifiedTable[LONGPROF_UTMN]), 3, "Long Profile"),

Then created a relationship between the DynamicColumns table and UnifiedTable. Then drag in the DynamicColumns parameter as the table.

[luisoliveira89]

Hi @Howeitzer ,

If your fields are in separated tables my suggestion would be creating a new table with DISTINCT values from both tables and then creating a relationship between them.

Since you mentioned you have SQL tables connnected I imagine you are using Direct Query or Import Data, you can create it using either on Edit Query or simply using DAX to  create a new table. Something like:

NewTable =
UNION (
     DISTINCT ( TableA[ColumnA] ),
     DISTINCT ( TableB[ColumnB] )
)

Regards,

Luis Oliveira

[Nasif_Azam]

Hey @Howeitzer,

Field parameters are not a good tool for cascading slicers across disconnected tables. Append the tables in Power Query into one unified table with a Source Table label column, then use three plain slicers. Cascading works natively, no DAX.

Why the field parameter approach breaks down?

A field parameter swaps which column a visual references, but it can't carry a filter from one disconnected table into another. The moment you add a third slicer for SURVEY_FULL_NAME, Power BI throws:"You are using fields that don't have a supported set of relationships" - ConditionNotHierarchicallyRelated

You can try the following fix:

1. Add a Source Table column to each source query

(Add Column → Custom Column → e.g. "Long Profile", "KP", "Depth"). Set type to Text.

 

2. Home → Append Queries → Append Queries as New → add all source queries → rename the result Pipelines_Unified. Columns unique to one table (DEPTH_M, KP_VALUE, COVER_M) come through with nulls where they do not apply that's expected.

3. Disable load on the original queries (right-click → uncheck Enable load), Close & Apply, and delete the old Pipeline Switcher field parameter.

4. Add three plain slicers from Pipelines_Unified:

• Source Table
• PIPELINE_FULL_NAME
• SURVEY_FULL_NAME

Output:

Each selection narrows the next slicer automatically that needs no DAX, no field parameter, no Show values of selected field:

Long Profile selected, all pipelines/surveys for that table visible.

Long Profile + Pipeline_A → only the 2 LongProf surveys. 

KP + Pipeline_A → only the 3 KP surveys

Depth + Pipeline_A → only the 2 Depth surveys

Also attached the PBIX file you can take a look and if there any concern feel free to reach back.

Best Regards,
Nasif Azam

[Howeitzer]

HI @Nasif_Azam . Much appreciated. I took this approach and basically concatenated all of my tables into one with the new SourceTable attribute. Created my three slicers, Table Select, Pipeline Select, and Survey Select. This works great! Is it possible to show the records in a table visual but only show the columns that are relevant the table selected? So out of all columns in the combined table, if the user selects Long Profile, it only shows the Long Profile columns plus three global columns. Each column in the combined table has a prefix so for example Long Profile columns start with 'LONGPROF_' so might be possible to add some code for this?

[Nasif_Azam]

Hey @Howeitzer,

For the dynamic columns piece, the cleanest approach is to build one table visual per source (each with only its relevant columns + the 3 global ones), stack them in the same canvas spot, and use bookmarks + buttons to swap which one is visible. The buttons replace your Source Table slicer and drive both the visibility and the filter in a single click.

Step-1: Build one table visual per source

Create three separate Table visuals from Pipelines_Unified:

• Long Profile table → PIPELINE_FULL_NAME, SURVEY_FULL_NAME, plus all your LONGPROF_* columns
• KP table → PIPELINE_FULL_NAME, SURVEY_FULL_NAME, plus all your KP_* columns
• Depth table → PIPELINE_FULL_NAME, SURVEY_FULL_NAME, plus all your DEPTH_* columns (or whatever your prefix is)

Stack all three at the exact same X, Y, Width, Height (Format pane → General → Properties → Size and Position).

Long Profile Table

KP Table

Depth Table

Step-2: Create show/hide measures

Create one measure per source:

DAX:

Show_LongProfile = IF(SELECTEDVALUE('Pipelines_Unified'[Source Table]) = "Long Profile", 1, 0)
Show_KP          = IF(SELECTEDVALUE('Pipelines_Unified'[Source Table]) = "KP", 1, 0)
Show_Depth       = IF(SELECTEDVALUE('Pipelines_Unified'[Source Table]) = "Depth", 1, 0)

Step-3: Apply the show/hide measure as a visual-level filter

For each table:

1. Click the table → Filters pane → Filters on this visual
2. Drag the matching measure (Show_LongProfile for the Long Profile table, etc.) into the filter section
3. Set Show items when the value is 1 → Apply filter

This ensures only the relevant table renders data even if the bookmark logic ever fails or the page loads without a button click.

Step-4: Rename the visuals in the Selection pane

View → Selection pane. Double-click each table and rename to Table_LongProfile, Table_KP, Table_Depth. This makes the next steps far easier.

Step-5: Create a bookmark per source

View → Bookmarks pane. For each source:

1. In the Selection pane, hide the other two tables (eye icon) — show only the one you're bookmarking.
2. Click the Source Table slicer and select that source (e.g., select "Long Profile" for the Long Profile bookmark). This is what makes the button apply the filter.
3. Bookmarks pane → Add → rename to BM_LongProfile (then BM_KP, BM_Depth).

Repeat for KP and Depth.

Step-6: Add three navigation buttons

Insert → Buttons → Blank. For each button:

• Format → Button → Style → Text → label it Long Profile, KP, or Depth
• Format → Action → ON → Type: Bookmark → pick the matching bookmark

Place them in a row above the table area.

Step-7: Hide (or delete) the Source Table slicer

The buttons now drive both the source filter and the column visibility, so the slicer is redundant. Either delete it, or hide it via the Selection pane to keep the bookmark logic intact but cleaner UI.

Step-6: Test

In Power BI Desktop, Ctrl+click the buttons to activate them (in the published report, single click works for end users):

 

The Pipeline and Survey slicers continue to cascade as before. Only the table visual swaps and the source filter changes.

Also attached the PBIX file you can take a look and if there any concern feel free to reach back.

Best Regards,
Nasif Azam

[SEMattis]

Hej @Howeitzer! 

I'm wondering if you need to create a field parameter to solve you issue. Could you perhaps introduce a bridge table that sits between your two tables IPMS GIS_USER_LONGPROF and IPMS KP_REPORT_ALL in which all unique PIPELINE_FULL_NAME values as well as the unique values from SURVEY_FULL_NAME are? You could then create relationships between the bridge table and the aforementioned two tables. To make it clear, this is how I would design the bridge table (Without including PowerQuery or DAX code)

TableName	PIPELINE_FULL_NAME (Key on which to create relationship between bridge and two fact tables?)
IPMS GIS_USER_LONGPROF	PIPELINE_A
IPMS KP_REPORT_ALL	PIPELINE_C
IPMS KP_REPORT_ALL	PIPELINE_Q
IPMS GIS_USER_LONGPROF	PIPELINE_O

Then add the TableName from the bridge table to the slicer (and the pipeline names if you want a hierarchical slicer). It should help you filter the two fact tables.

A small proposal, not sure if I cover the full ask of your question.

[AnkitKukreja]

Hi! @Howeitzer 

If I could understand you probelm correctly then you can try to create a dimension table out of your current table for SURVEY_FULL_NAME and then connect them with exisitng table. If this is not the case, then please share some sample data (without PII) and with the expected output that helps in understanding the data better.