Forum Discussion
Filter a disconnected table with a slicer
Following suggestions of submitting a question, this is an improved version of my original question: https://community.powerbi.com/t5/Desktop/Display-two-rows-with-multiple-column-values/m-p/1103049#M510219
1. Given two related tables related using field PID.
Table 1 - Test Run
| PID | Service | Count | Return | Duration |
| 1068 | A | 128 | 0 | 1024.595 |
| 1200 | B | 128 | 0 | 508.1637 |
| 1404 | B | 110 | 0 | 493.2852 |
| 1200 | B | 128 | 211.48 | 419.2638 |
| 1404 | A | 128 | 164.82 | 383.6828 |
| 7148 | B | 128 | 211.39 | 449.0829 |
| 7204 | B | 0 | 421.8271 | |
| 7924 | OD | 126 | 917.14 | 1193.8 |
| 177964 | O | 127 | 386.6 | 769.1757 |
| 183668 | B | 128 | 207.71 | 1119.463 |
| 183992 | B | 128 | 205.24 | 487.8962 |
| 173968 | O | 127 | 517.65 | 877.118 |
Table Two - Tests workflows
| PID | Workflow number | Trace result test | HTTP result success |
| 177964 | 1 | Fail | FALSE |
| 183668 | 1 | Fail | FALSE |
| 183992 | 1 | Pass | TRUE |
| 1068 | 2 | Pass | TRUE |
| 1200 | 2 | Pass | TRUE |
| 173968 | 3 | Pass | TRUE |
| 177964 | 3 | Fail | TRUE |
| 183668 | 3 | Fail | TRUE |
| 183992 | 3 | Pass | TRUE |
| 1068 | 4 | Pass | TRUE |
| 1200 | 4 | Pass | TRUE |
| 1404 | 4 | Pass | TRUE |
| 1556 | 4 | Pass | TRUE |
| 177964 | 5 | Fail | TRUE |
| 183668 | 5 | Fail | TRUE |
| 183992 | 5 | Pass | TRUE |
| 1068 | 6 | Pass | TRUE |
| 1200 | 6 | Pass | TRUE |
| 1404 | 6 | Pass | TRUE |
I also have a disconnected table below with more details on each workflow and a baseline row for each workflow. (Note please ignore values of the sum of Table A and Table B not equal to Row count - I removed a few columns)
| Slicer | Baseline | ProcessID | Workflow number | Table count | Row count | Table A | Table B |
| 0 | Yes | 1 | 6 | 31699 | 16492 | 15217 | |
| 1068 | 1068 | 1 | 6 | 31709 | 16492 | 15217 | |
| 1200 | 1200 | 1 | 6 | 31709 | 16492 | 30434 | |
| 1404 | 1404 | 1 | 6 | 31709 | 16492 | 15217 | |
| 0 | Yes | 2 | 11 | 37250 | 1278 | 15217 | |
| 1068 | 1068 | 2 | 11 | 37260 | 1288 | 15217 | |
| 1200 | 1200 | 2 | 11 | 37260 | 2576 | 30434 | |
| 1404 | 1404 | 2 | 11 | 37260 | 1288 | 15217 | |
| 0 | Yes | 3 | 3 | 506 | 161 | 347 | |
| 1068 | 1068 | 3 | 3 | 509 | 0 | 347 | |
| 1200 | 1200 | 3 | 3 | 509 | 0 | 694 | |
| 1404 | 1404 | 3 | 3 | 509 | 0 | 347 | |
| 0 | Yes | 4 | 3 | 427 | 137 | 290 | |
| 1068 | 1068 | 4 | 3 | 427 | 0 | 290 | |
| 1200 | 1200 | 4 | 3 | 427 | 0 | 580 | |
| 1404 | 1404 | 4 | 3 | 427 | 0 | 290 | |
| 0 | Yes | 5 | 3 | 3110 | 1305 | 1705 | |
| 1068 | 1068 | 5 | 3 | 3111 | 0 | 1706 | |
| 1200 | 1200 | 5 | 3 | 3111 | 0 | 3412 | |
| 1404 | 1404 | 5 | 3 | 3111 | 0 | 1706 | |
| 0 | Yes | 6 | 3 | 344 | 130 | 214 | |
| 1068 | 1068 | 6 | 3 | 343 | 0 | 214 | |
| 1200 | 1200 | 6 | 3 | 343 | 0 | 428 | |
| 1404 | 1404 | 6 | 3 | 343 | 0 | 214 |
The desired visual for this table is to show the workflow baseline vs the PID result to compare workflow details..
| Slicer | Baseline | ProcessID | Workflow number | Table count | Row count | Table A | Table B |
| 0 | Yes | 1 | 6 | 31699 | 16492 | 15207 | |
| 0 | 1068 | 1 | 6 | 31709 | 16492 | 15217 | |
| 0 | Yes | 2 | 11 | 37250 | 1278 | 15217 | |
| 0 | 1068 | 2 | 11 | 37260 | 1288 | 15217 | |
| 0 | Yes | 3 | 3 | 506 | 161 | 347 | |
| 0 | 1068 | 3 | 3 | 509 | 162 | 347 | |
| 0 | Yes | 4 | 3 | 427 | 137 | 290 | |
| 0 | 1068 | 4 | 3 | 427 | 137 | 290 | |
| 0 | Yes | 5 | 3 | 3110 | 1305 | 1705 | |
| 0 | 1068 | 5 | 3 | 3111 | 1305 | 1706 | |
| 0 | Yes | 6 | 3 | 344 | 130 | 214 | |
| 0 | 1068 | 6 | 3 | 343 | 130 | 213 |
3. I'd like to create a measure that will change the value of [Slicer] field in the disconnected table from PID to 0 so that a visual filter can be used on the value '0'. The row whose PID value is changed will be based on the selected PID on a slicer that filters Tables 1 and 2.
4 Replies
v-xicaiCommunity Support
Hi rena9994 ,
You can create measure Filter1 like DAX below ,then put measure Filter1 in the Visual Level Filter of table visual displaying the disconnected table , and setting the Filter1 as "is not blank".
Filter1 = IF(MAX([Slicer])=0,1,BLANK())
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Amy, thanks for your reply. That's the last part and I have a good handle on that part already. The part I have a problem with is changing the Slicer value in the disconnected table from PID to 0, once the PID is selected from the slicer that filters the two connected tables
.
- v-xicai
Hi rena9994 ,
You may create an intermediate table like DAX below, create relationships with your original two tables, then put 'Intermediate Table'[PID] into slicer visual to filter the other two tables.
Intermediate Table = UNION(DISTINCT(Table1[PID]), DISTINCT(Table2[PID]))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
