Filter/no Filter for specific entry between two tables

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

Thanks for your reply. I will try idea no 1.

I'll come back on this after my vacation