Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
What result are you getting if you simply add a selectedvalue() over the salaryview column here?
SalaryFilter = IF(and(SELECTEDVALUE(TableA[SalaryView]) = 0, TableB[ExpenseCode] IN {"ExpSal1", "ExpSal2", "ExpSal3"}), 0, 1)
Br,
Johannes
What result are you getting if you simply add a selectedvalue() over the salaryview column here?
SalaryFilter = IF(and(SELECTEDVALUE(TableA[SalaryView]) = 0, TableB[ExpenseCode] IN {"ExpSal1", "ExpSal2", "ExpSal3"}), 0, 1)
Br,
Johannes
You Star! 🌟 Ah man, it was so simple.... 😫
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |