Forum Discussion
Finding the differences between 2 identical tables filtered differently through slicers
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
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.
4 Replies
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)
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
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.
Thanks so much Anonymous !
