Forum Discussion
Drill through excluding specific record
Hi all,
I have two visuals (tables), where the user can click on a row in visual 1, and the table in visual 2 should filter according to the selected row's key fields. This is currently done with the drill-through option.
Both tables have the same columns. So let's say these are columns 1 through 3. The keys to filter the data in visual 2 are in columns 1 and 2. Each record can (but does not have to) appear in both datasets. What I want to achieve is that visual 2 shows all records where the keys in columns 1 and 2 are the same as in the selected visual 1 row, but specifically excludes the records where columns 1, 2 and 3 are the same. In other words, if the exact same record appears twice (in both datasets), it should not show in the second when selected in the first.
Can this be achieved? TIA!
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
6 Replies
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
Hi Jensman,
In order to solve your issue, I have some questions:
1) Are the two datasets from the same table or two different tables?
(a single table with a "Source" flag column, or two physically separate tables in the model)
2) Are Column1/Column2/Column3 from the same underlying columns in both visuals, or are they from different tables mapped to the same concept?
3) Can you afford to add Column3 as a drill-through field on the destination page?
Hi 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
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
