Filter a disconnected table with a slicer

[rena9994]

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#M5...

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.

[v-xicai]

Hi   @rena9994 ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

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

[rena9994]

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.