How to apply a measure filter based on path checking to a map visual

[Singularity9]

I have a map which feeds off TableA, using latitude and longitude. I want to filter the map so it only returns values where the data was entered by a subordinate of the manager who is using the page, using USERPRINCIPALNAME() to check their employee number (emp no) against a table called 'Employee - Main Data'

Is_Subordinate =
IF (
    PATHCONTAINS (
        PATH (
            'Employee - Main Data'[Emp No],
            'Employee - Main Data'[Supervisor Search]
        ),
        LOOKUPVALUE (
            'Employee - Main Data'[Emp No],
            'Employee - Main Data'[Email Address], USERPRINCIPALNAME ()
        )
    ),
    1,
    0
)

I can't apply this measure as a filter to visuals as it causes the "a table of multiple values was returned" error, but I can use it as a part of measures like this -

calculate(measure1, Is_Subordinate=1)

- where it works fine. However, a map visual doesn't use measures, it needs the unsummarized longitude and latitude as columns. So I can't use a measure in place of them, and I can't apply the measure as a filter because that causes the "a table of multiple values was returned" error. So when a user first opens up the dashboard, they will see all data points on the map, rather than just those for their own team. I've tried other measures to narrow it down, such as 

Test =
CALCULATE (
    DISTINCTCOUNT ( 'Employee - Main data'[Emp No] ),
    FILTER (
        'Employee - Main data',
        [Is_Subordinate] = "1"
            && PATHCONTAINS (
                PATH (
                    'Employee - Main Data'[Emp No],
                    'Employee - Main Data'[Supervisor Search]
                ),
                [RLS Emp]
            )
    )
)

but they don't work - the above just returns 1 for everyone, even those who the user does not have as a subordinate. Can anyone help?

[v-dineshya · Accepted solution]

Hi @Singularity9 ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps.

1. Created two tables (Employee, Table) with sample data, please refer below snap.

2.  Created "Supervisor_Path" column in Employee table  with below DAX.

Supervisor_Path = PATH('Employee'[Emp No], 'Employee'[Supervisor Search])

3. Created  "Is_Subordinate_Measure" measure that checks whether a row is from a subordinate 

Is_Subordinate_Measure =

VAR CurrentUserEmpNo =

    LOOKUPVALUE(

        'Employee'[Emp No],

        'Employee'[Email Address],

        USERPRINCIPALNAME()

    )

VAR RowEmpNo =

    SELECTEDVALUE('Table'[Emp No])

VAR PathToCheck =

    LOOKUPVALUE(

        'Employee'[Supervisor_Path],

        'Employee'[Emp No],

        RowEmpNo

    )

RETURN

    IF (

        PATHCONTAINS(PathToCheck, CurrentUserEmpNo),

        1,

        0

    )

4. Selected "Azure Map"  and dragged below fields.

Table[Latitude] as Latitude & Table[Longitude] as Longitude. Add the "Is_Subordinate_Measure" to Filters on this visual. Set the filter to "1".

For testing, Please check below things.

5. Selected "Table" visual , and draged the required columns from "Employee" table.

6. In modeling tab -> View as -> and mentioned email address for testing.

7. Please refer the output snap and attched PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[Singularity9]

This worked, thank you! I had to remove the relationship between the two tables and modify other measures to account for that, but the end result is working well. 

[OwenAuger]

Hi @Singularity9 

There are a few ways you could set this up, with logic similar to what you posted above.

Here's one example (PBIX attached with dummy data):

1. I would recommend storing the path in a calculated column (for performance reasons):

Employee Path = 
PATH ( 'Employee - Main Data'[Emp No], 'Employee - Main Data'[Supervisor Search] )

2. Relationship between 'Employee - Main Data'[Emp No] & TableA[Emp No] (1-to-many)

3. The measure for filtering the map visual could then be this (filtered equal to 1 on the map visual):

Subordinate Location Flag = 
VAR CurrentEmpNo =
    LOOKUPVALUE ( 'Employee - Main Data'[Emp No], 'Employee - Main Data'[Email Address], USERPRINCIPALNAME() )
VAR SubordinateLocationFlag =
    CALCULATE (
        INT ( NOT ISEMPTY ( TableA ) ),
        KEEPFILTERS (
            PATHCONTAINS ( 'Employee - Main Data'[Employee Path], CurrentEmpNo )
        ),
        KEEPFILTERS ( 'Employee - Main Data'[Emp No] <> CurrentEmpNo )
    )
RETURN
    SubordinateLocationFlag

I created a couple of similar measures to format the Employee table visual for demo purposes.

You could use a calculation group if you need to apply the filtering logic more widely.

Is this the sort of thing you were looking for?