Filtering "Access Level" from security table

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 [email protected], 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,