Forum Discussion
Drill through excluding specific record
Hi Jensman
Yes, this is possible, but I would not try to solve it with drill-through filters only.
The drill-through should mainly be used to pass the selected row context from visual 1. Then, on visual 2, use a measure to decide which rows should remain visible.
In your case, the logic should be:
```text
Column1 + Column2 must match the selected row from visual 1
but
Column1 + Column2 + Column3 must not be exactly the same record
```One possible approach is to create two key columns in both tables:
```DAX
Key_12 =
COMBINEVALUES(
"|",
'Table'[Column1],
'Table'[Column2]
)
``````DAX
Key_123 =
COMBINEVALUES(
"|",
'Table'[Column1],
'Table'[Column2],
'Table'[Column3]
)
```Then create a measure for visual 2:
```DAX
Show in Visual 2 =
VAR SelectedKey12 =
SELECTEDVALUE ( 'Table1'[Key_12] )VAR SelectedFullKey =
SELECTEDVALUE ( 'Table1'[Key_123] )VAR CurrentKey12 =
SELECTEDVALUE ( 'Table2'[Key_12] )VAR CurrentFullKey =
SELECTEDVALUE ( 'Table2'[Key_123] )RETURN
IF (
NOT ISBLANK ( SelectedKey12 )
&& CurrentKey12 = SelectedKey12
&& CurrentFullKey <> SelectedFullKey,
1,
0
)
```Add this measure to the visual-level filters of visual 2 and set it to:
```text
Show in Visual 2 is 1
```The important point is to make sure that `Column3` or `Key_123` is not directly filtering visual 2 through the drill-through/page filters. If Power BI already filters visual 2 down to the exact full record before the measure is evaluated, the measure will not be able to bring back the other matching rows.
So the safer setup is:
* use drill-through only to pass the selected row context from Table1
* compare `Column1 + Column2` in the measure
* exclude the exact same `Column1 + Column2 + Column3` combination in the measure
* avoid applying `Column3` as a direct drill-through filter on Table2If this still does not work as expected, please share a small sample PBIX or a simplified model with dummy/non-sensitive data. The important part would be to include the two tables, columns 1–3, the current relationships, the drill-through setup, and one example of the expected output for a selected row. With that, it would be much easier to suggest the exact DAX/model setup.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Community SupportHi Jensman ,
Thank you for reaching out to the Microsoft Community Forum.
Yes, this can be achieved in Power BI, but not directly with standard drill-through behavior. You will need to add a custom filtering logic (measure or calculated column) to explicitly exclude the exact matching record.
Please try below workaround.
Create a Measure to Filter Visual 2, you can create a DAX measure that flags rows to include/exclude dynamically. Capture selected row values, because drill-through passes filters, you can reference them using SELECTEDVALUE.
ExcludeExactMatch =
VAR SelectedCol1 = SELECTEDVALUE(Table[Column1])
VAR SelectedCol2 = SELECTEDVALUE(Table[Column2])
VAR SelectedCol3 = SELECTEDVALUE(Table[Column3])
RETURN
IF(
Table[Column1] = SelectedCol1 &&
Table[Column2] = SelectedCol2 &&
Table[Column3] = SelectedCol3,
0, -- Exclude exact match
1 -- Keep others
)
Apply to Visual 2 and add this measure to Visual 2 Filters pane and Set filter " ExcludeExactMatch = 1".
Note: If Visual 1 and Visual 2 come from different tables, try below code.
ExcludeExactMatch =
VAR SelectedCol1 = SELECTEDVALUE(Table1[Column1])
VAR SelectedCol2 = SELECTEDVALUE(Table1[Column2])
VAR SelectedCol3 = SELECTEDVALUE(Table1[Column3])
RETURN
IF(
Table2[Column1] = SelectedCol1 &&
Table2[Column2] = SelectedCol2 &&
Table2[Column3] = SelectedCol3,
0,
1
)
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- v-dineshya
Hi Jensman ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh