Finding the differences between 2 identical tables filtered differently through slicers

[drem]

Hi, I have 2 tables which are identical and filtered through 2 different slicers for column "Occupation". I would like to create a new table which shows the differences between the filtered tables for column "Skill". Please see the following:

Original data Table 1 and 2 (unfiltered)

Occupation	Skill
Nurse	Communication
Nurse	Other
Doctor	Perform operation
Doctor	Communication
Doctor	Other

----------------------------------------------------------------------------

Table 1 (filtered Occupation column on Nurse)

Occupation	Skill
Nurse	Communication
Nurse	Other

Table 2 (filtered Occupation column on Doctor)

Occupation	Skill
Doctor	Perform operation
Doctor	Communication
Doctor	Other

I would like Table 3 which shows the difference in column "Skill" between filtered Table 1 and filtered Table 2

Skill

Surgery

This is what I would like to achieve:

I have tried using parameters in the backend but I was not able to link it with the slicers on the front end.

I appreciate any help with helping me solve this issue.

Thank you

[drem]

Thank you @swikritee_p  for your response. I have followed your suggestion but I still can't get Table 3 to display the correct result.

Table 3 should only return 1 row "Doctor | Perform operation" because it exists in Table 2 but not Table 1. The screenshot shows it is returning all values in the combined table.

Also " You need a table with combine values " - I created a new table using the merge query (combine Table 1 and Table 2 using Full Outer Join) on column "Occupation", can you please clarify if this is correct?

If you could attach your working solution that would be great. 

Thank you

[Anonymous · Accepted solution]

Hi  @drem 

You can refer to the following solution.

1.You can create two tables, then put the values of the two tables to two slicers.

Slicer 1 = SUMMARIZE('Table',[Occupation])

Slicer 2 = SUMMARIZE('Table',[Occupation])

2.Create the the following measures.

Measure_Slicer1 = IF(SELECTEDVALUE('Table'[Occupation]) in VALUES('Slicer 1'[Occupation]),1,0)

Measure_Slicer2 = IF(SELECTEDVALUE('Table'[Occupation]) in VALUES('Slicer 2'[Occupation]),1,0)

put the measure_slicer1 to the table visual filter(the table is filted to the doctor)

put the measure_slicer2 to the table visual filter(the table is filted to the nurse)

put the measure3 to the table visual filter(the table is filted to the difference)

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[drem]

Thanks so much @Anonymous ! 

[swikritee_p]

@drem , First of all you need two disconnected table Occupation

Occupation 1 = distinct(union(distinct(Table1[Occupation]),distinct(Table2[Occupation])))

Occupation 2 = distinct(union(distinct(Table1[Occupation]),distinct(Table2[Occupation])))

Use them in slicer 

Now create measures 

M1 = //for Table1 

countrows(Filter( Table1,Table1[Occupation] in values(Occupation1[Occupation]) )

M2 = //for Table2 

countrows(Filter( Table2,Table2[Occupation] in values(Occupation2[Occupation]) )

M3 = //for Visual Table3 - You need a table with combine values 

countrows(Filter( Table3, ( Table3[Occupation] in values(Occupation2[Occupation]) ||  Table3[Occupation] in values(Occupation1[Occupation])  ))

Or you can use except for a static table  

table3 =  except(Table1, Table2)