I have two tables (simplified for my question):
Table A (Factual Data)
OrganisationalRole
DirTeamLink (Directorate Team Link)
SalaryView
Table B (Financial Data)
DirTeamLink
Directorate
Team
ExpenseCode
NetValue
This is (unavoidably) a many to many relationship with table A filtering table B linked on the DirTeamLink - as one Organisational Role could be affiliated to many Directorates and/or many Teams.
The SalaryView column is a 1 or 0 (zero) depending on the OrganisationalRole. (1 = Can see salaries, 0 = Cannot see salaries)
The report has RLS on the OrganisationalRole - so the salary view column will always be filtered to either one or zero.
Table B has expense codes which could be related to any Directorates and/or Teams too.
Within the Expense Codes are some related to Salaries (Example: ExpSal1, ExpSal2, ExpSal3).
To write what I want in simple terms:
If SalaryView = 0 and the ExpenseCodes not one of the salary codes (not in ExpSal1, ExpSal2, ExpSal3)
then hide the salary expense code rows (or apply a 0/zero to the row so I may filter it)
else show all (apply a 1 to the row)
Or
SalaryFilter = IF(and(TableA[SalaryView] = 0, TableB[ExpenseCode] IN {"ExpSal1", "ExpSal2", "ExpSal3"}), 0, 1)
** I know the above does not work, I just wanted to write it in a way to make it easy to read
Seems so simple when I type it out but I cannot seem to get DAX to create the filter as it requires the SalaryView to be a measure in order that Table B can use its actual value. I've tried a lot of different things but my mind is now mush! I beleive there is a simple solution but I just cannot see the wood from the trees right now.
Can anyone help?
