How to access intersection of two cascading slicers in a measure?

[jporali]

Hi everyone,

I'm currently working on a Power BI tool, that allows users to view our products' component life cycles, and "replace" certain component life cycles, if needed. I want the users to be able to select which components are viewed in a matrix visual, and then select which components will be replaced.

So essentially, I have a scenario with two slicers on the same field where:

• Slicer 1 filters the matrix visual and cascades to Slicer 2
• Slicer 2 is disconnected and only affects measures (not the visual)

My goal is to create a measure that identifies whether the current row value exists in the intersection of both slicer selections.

The problem: Within the matrix visual's row context, standard functions like VALUES() or ALLSELECTED() always return the broader selection from Slicer 1, so my measure always returns TRUE. I need to somehow capture the "smaller" selection that represents where both slicers overlap.

I've tried:

• Using INTERSECT() with VALUES() from both tables
• Using ALLSELECTED() to isolate different filter contexts
• Creating a disconnected slicer table

The cascading relationship works fine, but I can't figure out how to access both filter contexts simultaneously in the measure to find their intersection.

Has anyone solved a similar problem? Is there a way to capture the filter context from a disconnected slicer separately from the related table's filter context within a measure evaluated in row context?

Thanks in advance!

[burakkaragoz · Accepted solution]

Hi @jporali ,

This is a classic "Disconnecting Context" challenge.

The reason your INTERSECT or ALLSELECTED attempts are returning the "broader" selection (Slicer 1) is likely because you are trying to compare the column against itself, or you are not isolating the Row Context (the specific product in the matrix row) from the Filter Context (the Slicer selections).

To solve this, you need a clear physical separation in your data model. You cannot effectively have one table act as both "Slicer 1" and "Slicer 2" with different scopes in the same visual without a disconnected table helper.

Here is the pattern to solve the "Intersection of Selection" problem:

Step 1: Verify the "Disconnected" Table Ensure you have two distinct tables.

1. Product Table: This is your main table. It drives Slicer 1 and the Rows of your Matrix.

2. Product_Select Table: This is a copy of the Product ID/Name column. It effectively drives Slicer 2.

   • Crucial: There should be NO active relationship between Product and Product_Select.

Step 2: The Measure (Row vs. Set) You need a measure that asks: "Is the Item in the current Matrix Row (Product Table) present in the list selected in Slicer 2 (Product_Select Table)?"

Is Replacement Candidate = 
VAR CurrentRowItem = SELECTEDVALUE('Product'[ComponentID]) -- The item in the matrix row
VAR Slicer2Selection = VALUES('Product_Select'[ComponentID]) -- The list selected in Slicer 2

RETURN
    IF(
        CurrentRowItem IN Slicer2Selection,
        TRUE(), 
        FALSE()
    )

Step 3: Handling the "Cascade" You mentioned the cascading works fine, but for clarity: since the tables are disconnected, Slicer 2 won't automatically filter down to Slicer 1's choices unless you force it.

• The Trick: Add a "Visual Level Filter" to the Slicer 2 Visual.

• Measure for Filter: CountOfrows = INT( SELECTEDVALUE('Product_Select'[ID]) IN VALUES('Product'[ID]) )

• Set this filter to is 1. Now Slicer 2 only shows options that survived Slicer 1.

Why your previous attempts failed:

• VALUES() in Row Context: When used on the main table inside the matrix, VALUES() returns only the single item visible in that row.

• ALLSELECTED(): Returns the Slicer 1 selection (the broad list).

• Intersection: INTERSECT( Row_Item, Slicer1_List ) is just Row_Item. You needed to intersect Row_Item with Slicer2_List.

By using the disconnected table (Product_Select) for the second slicer, you create a separate "bucket" of selections that doesn't interfere with the main visual's query, allowing you to compare them cleanly in the measure.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[krishnakanth240]

Hi @jporali 

VAR CurrentComponent =
SELECTEDVALUE ( Components[ComponentID] )

VAR ReplacementSelection =
VALUES ( Components_Disconnected[ComponentID] )

Is In Both Slicers :=
VAR CurrentComponent =
SELECTEDVALUE ( Components[ComponentID] )

VAR ReplacementSelection =
VALUES ( Components_Disconnected[ComponentID] )

RETURN
IF (
NOT ISBLANK ( CurrentComponent )
&& CONTAINS (
ReplacementSelection,
Components_Disconnected[ComponentID],
CurrentComponent
),
TRUE (),
FALSE ()
)

we can
Use it as a visual-level filter = TRUE

Use it to
Highlight rows
Enable replacement logic
Drive conditional formatting
Control buttons

[v-hjannapu]

Hi @jporali,

I would also take a moment to thank @cengizhanarslan  , @burakkaragoz  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @jporali,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[cengizhanarslan · Accepted solution]

you can do this, but you need to read each slicer’s selection from its own table, then intersect those sets outside the row context.

• Slicer 1 uses the related dimension (e.g. DimComponent[Component]) → filters the matrix.

• Slicer 2 uses a disconnected copy (e.g. ComponentSelector[Component]) and is cascaded by Slicer 1 (typically via a measure filter on the slicer visual or sync logic).

Is In Intersection =
VAR RowComponent =
    SELECTEDVALUE ( DimComponent[Component] )

-- what Slicer 1 allows (broader set)
VAR S1 =
    CALCULATETABLE (
        VALUES ( DimComponent[Component] ),
        ALLSELECTED ( DimComponent[Component] )
    )

-- what user picked in the disconnected slicer (smaller set)
VAR S2 =
    VALUES ( ComponentSelector[Component] )

VAR IntersectSet =
    INTERSECT ( S1, S2 )
RETURN
IF ( RowComponent IN IntersectSet, TRUE (), FALSE () )

• VALUES(ComponentSelector[Component]) gives you only slicer 2 picks (it’s disconnected, so it won’t be overwritten by the matrix row context from DimComponent).

• ALLSELECTED(DimComponent[Component]) captures slicer 1’s effective selection (the matrix filters).

• INTERSECT() gives the overlap, then you test the current row value.

[burakkaragoz · Accepted solution]

Hi @jporali ,

This is a classic "Disconnecting Context" challenge.

The reason your INTERSECT or ALLSELECTED attempts are returning the "broader" selection (Slicer 1) is likely because you are trying to compare the column against itself, or you are not isolating the Row Context (the specific product in the matrix row) from the Filter Context (the Slicer selections).

To solve this, you need a clear physical separation in your data model. You cannot effectively have one table act as both "Slicer 1" and "Slicer 2" with different scopes in the same visual without a disconnected table helper.

Here is the pattern to solve the "Intersection of Selection" problem:

Step 1: Verify the "Disconnected" Table Ensure you have two distinct tables.

1. Product Table: This is your main table. It drives Slicer 1 and the Rows of your Matrix.

2. Product_Select Table: This is a copy of the Product ID/Name column. It effectively drives Slicer 2.

   • Crucial: There should be NO active relationship between Product and Product_Select.

Step 2: The Measure (Row vs. Set) You need a measure that asks: "Is the Item in the current Matrix Row (Product Table) present in the list selected in Slicer 2 (Product_Select Table)?"

Is Replacement Candidate = 
VAR CurrentRowItem = SELECTEDVALUE('Product'[ComponentID]) -- The item in the matrix row
VAR Slicer2Selection = VALUES('Product_Select'[ComponentID]) -- The list selected in Slicer 2

RETURN
    IF(
        CurrentRowItem IN Slicer2Selection,
        TRUE(), 
        FALSE()
    )

Step 3: Handling the "Cascade" You mentioned the cascading works fine, but for clarity: since the tables are disconnected, Slicer 2 won't automatically filter down to Slicer 1's choices unless you force it.

• The Trick: Add a "Visual Level Filter" to the Slicer 2 Visual.

• Measure for Filter: CountOfrows = INT( SELECTEDVALUE('Product_Select'[ID]) IN VALUES('Product'[ID]) )

• Set this filter to is 1. Now Slicer 2 only shows options that survived Slicer 1.

Why your previous attempts failed:

• VALUES() in Row Context: When used on the main table inside the matrix, VALUES() returns only the single item visible in that row.

• ALLSELECTED(): Returns the Slicer 1 selection (the broad list).

• Intersection: INTERSECT( Row_Item, Slicer1_List ) is just Row_Item. You needed to intersect Row_Item with Slicer2_List.

By using the disconnected table (Product_Select) for the second slicer, you create a separate "bucket" of selections that doesn't interfere with the main visual's query, allowing you to compare them cleanly in the measure.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.