Filtering "Access Level" from security table

[WZorn]

I have an access table that has two flag columns.  These flags tell if the user can view all departments in an organization or if they only have department level access.  If they have department level access then the department they have access to is specified in a department code column.  The employees with department level access can have access to multiple departments.

In case you are wondering, yes this is used for row-level security.  Some employees in the table can have org level access and dept level access.  I need to allow them to select which security-level they are viewing.  (Hence the access-level filter)

Access Table (not linked to other tables in model)

Email	Org Flag	Dept Flag	Dept Code
john@abc.org	TRUE	FALSE
beth@abc.org	TRUE	FALSE
beth@abc.org	FALSE	TRUE	1
beth@abc.org	FALSE	TRUE	3

I have another table that just lists out the different access levels.  A slicer will be used to select the Name column on this table.

Index	Name
1	Organizational
2	Departmental

Here is a simple Fact table:

Dept Code	Data
1	100
2	350
1	200
1	275
5	150

And a dimenstion table:

Dept Code	Dept Name
1	Admin
2	Business
3	Accounting
4	Engineering
5	Shipping

The measure below was my attempt to create the filter.  

security level filter = 
VAR user = USERPRINCIPLENAME()
VAR selected_level = SELECTEDVALUE('Access Level'[Name])
VAR org_flag = 
	LOOKUPVALUE(
		'Access Table'[Org Flag],
		'Access Table' [Email],
		user,
		'Access Table'[Org Flag],
		TRUE,
		FALSE
	)
VAR dept_flag = 
	LOOKUPVALUE(
		'Access Table'[Dept Flag],
		'Access Table' [Email],
		user,
		'Access Table'[Dept Flag],
		TRUE,
		FALSE
	)
	
RETURN
	SWITCH(
		TRUE,
		org_flag && selected_level == "Organizational", TRUE,    // Return TRUE to ALL Departments if Organizational level access
		dept_flag && selected_level == "Departmental",
			LOOKUPVALUE(
				'Access Table'[Dept Code],
				'Access Table'[Email],
				user,
				'Access Table'[Dept Code],
				MIN('Dim Table'[Dept Code]),
				'Access Table'[Dept Flag],
				TRUE
			) == MIN('Dim Table'[Dept Code]),	// Return TRUE if departmental security level is selected and department code is in access list
		FALSE
	)

I'm betting that I'm over-complicating this.  My intention was to use this to filter at the visual level so that data at the selected security level was shown, but when I insert it into "Filters on this Visual" I cant set any filter options.  The drop down arrow doesnt' do anything.

Any ideas to push me in the right direction?

[DataNinja777 · Accepted solution]

Hi @WZorn ,

You're on the right track, and your diagnosis is correct—the DAX is a bit overcomplicated, which is likely causing the filtering issue. The primary problem is the LOOKUPVALUE function, which isn't designed to handle cases where a user, like beth@abc.org, can have multiple permission rows. A more reliable method is to create a measure that builds a virtual list of the user's allowed departments and then checks against that list. This will consistently return a TRUE or FALSE value that the visual-level filter can use.

You can create the following measure to replace your original attempt. It's structured to be more robust and easier to read.

Security Filter = 
VAR CurrentUser = USERPRINCIPLENAME()
VAR SelectedLevel = SELECTEDVALUE('Access Level'[Name])

-- Check if the current user has Org Level permission anywhere in the access table
VAR HasOrgAccess =
    NOT ISEMPTY(
        FILTER(
            'Access Table',
            'Access Table'[Email] = CurrentUser && 'Access Table'[Org Flag] = TRUE
        )
    )

-- Create a list of department codes the current user has explicit access to
VAR UserAllowedDepts =
    CALCULATETABLE(
        VALUES('Access Table'[Dept Code]),
        'Access Table'[Email] = CurrentUser,
        'Access Table'[Dept Flag] = TRUE
    )

-- Get the department currently being evaluated in the visual's context
VAR CurrentDeptInVisual = SELECTEDVALUE('Dim Table'[Dept Code])

RETURN
    SWITCH(
        TRUE(),
        -- SCENARIO 1: User selects "Organizational" and has that permission
        SelectedLevel = "Organizational" && HasOrgAccess,
        TRUE,
        
        -- SCENARIO 2: User selects "Departmental" and the department is in their allowed list
        SelectedLevel = "Departmental" && CurrentDeptInVisual IN UserAllowedDepts,
        TRUE,
        
        -- If neither condition is met, hide the data
        FALSE
    )

To apply this logic, drag the new [Security Filter] measure into the "Filters on this visual" pane for the relevant chart or table. In the filter card's settings, set the condition to show items when the value "is 1" and click "Apply filter". The visual will now respond correctly to the selection made in your Access Level slicer, showing the appropriate data for the logged-in user.

This revised approach is better because it avoids potential LOOKUPVALUE errors by instead using NOT ISEMPTY(FILTER(...)) to confirm if a permission exists. It also uses the IN operator to efficiently check if the department being evaluated is part of the user's pre-filtered list of allowed departments (UserAllowedDepts). Crucially, this measure will always return a definitive TRUE or FALSE, which solves the problem of the filter options being disabled—an issue that typically happens when a measure returns a BLANK value.

Best regards,

[WZorn]

Wow.  That was fast.  I like how this simplifies things.  I'm going to give it a shot.  Thanks!