Forum Discussion
Filter/no Filter for specific entry between two tables
I have 2 tables, in the first table (T_FACT_RevCost) are departments and financial figures, in the second one are the departments (T_DIM_Department). The tables are 1-* linked together. Selecting a department in T_DIM_Department should filter the T_FACT_RevCost - except for the "special" departments HR and Controlling, they should see everything in the FACT table. If I select HR or Controlling in the visual, the filter should be removed. And no, choosing nothing in the visual is not an option
T_Department
| Department | NoFilter |
| Controlling | 1 |
| HR | 1 |
| Dep1 | 0 |
| Dep2 | 0 |
| Dep3 | 0 |
| Dep4 | 0 |
T_FACT_RevCost
| Department | Rev | Cost | Period |
| Controlling | 10 | 5 | 2021-07-01 |
| HR | 20 | 15 | 2021-07-01 |
| Dep1 | 30 | 25 | 2021-07-01 |
| Dep2 | 40 | 35 | 2021-07-01 |
| Dep3 | 50 | 45 | 2021-07-01 |
| Dep4 | 60 | 55 | 2021-07-01 |
| Controlling | 10 | 5 | 2021-08-01 |
| HR | 21 | 16 | 2021-08-01 |
| Dep1 | 31 | 17 | 2021-08-01 |
| Dep2 | 41 | 22 | 2021-08-01 |
| Dep3 | 51 | 32 | 2021-08-01 |
| Dep4 | 52 | 42 | 2021-08-01 |
Should display all departments / values
File:
Filter_Test.pbix – OneDrive (live.com)
Help is really appreciated
Toddy
2 Replies
Just create a separate table with departments that you'll put in the slicer as above (it'll be a disconnected table) and keep in there the (hidden) field DepartmentSlicer[NoFilter]. Once you have this, create a measure that will return 1 if 1) the currently visible department (this time you'll drag the department from the T_Department onto the visual) - you can use ISINSCOPE to check for the currently visible department - is the one selected in the slicer and at the same time NoFilter for it is 0, or 2) if NoFilter is 1 for the currently selected value in the slicer. This logical condition codes the behaviour you want for your rows. Then use the measure to filter the rows in your visual using the Filter Pane.
Of course, there are other ways to do it. You could create a table that would store the mappings between FromDepartmentID and ToDepartmentID. It would be a factless fact table. The FromDepartmentName would be exposed, so that it could be put on the slicer. Depending on your model and requirements, you could/should delete the T_Department table. The mapping table would be connected to your fact either directly (many-to-many) or via a (hidden) T_Department table. This all depends on the requirements. Then, you'd slice by FromDepartment and it would work the way you want thanks to the mapping table.
It would be much easier to demonstrate... but I don't have time (yet).
However, you should never forget that your models should never allow RI violations. If you neglect to ensure it, you'll be in trouble sooner or later.