How to search record containing value from a variable to return multiple Values

[Roy_tap]

I have 2 tables which contain User Access and Project Accrss:

First Table contain of User Access with Location (Dim_Access)

UserID	Location
xxx\abc	US
xxx\abc	CA
xxx\bcd	AU

Second table contain of Project Location as follows: (Dim_Project)

Project ID	Location	Revenue
P1	US, CA, AU	123
P2	US	234
P3	AU	345

I am getting the username and match with the Dim_Access table to get the location of the user with the following Syntax

    var getUser= USERNAME()
    var getLocation =
    CALCULATETABLE(VALUES(User_Access_D[Location]), FILTER(User_Access_D, User_Access_D[User]=getUser))

How can I get the list of Project for user xxx\abc based on the location that appears in the Location in the Dim_Proejct table with the following result by creating the Flag:

Project ID	Location	Revenue	Flag
P1	US, CA, AU	123	1
P2	US	234	1
P3	AU	345	0

How can i identify the Project based on the Location identify i.e. US, CA which is Project P1 and P2? (for user xxx\abc)

Thanks.

[tamerj1]

Hi @Roy_tap 
Please refer to attached sample file with the proposed solution. This distinct-counts the locations.

# Locations = 
VAR getUser = SELECTEDVALUE ( User_Access_D[UserID] ) -- USERNAME ( )
VAR getLocations =
    CALCULATETABLE ( 
        VALUES ( User_Access_D[Location] ), 
        User_Access_D[UserID] = getUser
    )
VAR allLocations = 
    DISTINCT (
        SELECTCOLUMNS ( 
            GENERATE ( 
                VALUES ( Project_Location_D[Location] ),
                VAR String = Project_Location_D[Location]
                VAR Items = SUBSTITUTE ( String, ", ", "|" )
                VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
                VAR T = GENERATESERIES ( 1, Length, 1 )
                RETURN
                    SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) )
            ),
            "Locations", [@Item]
        )
    )
RETURN
    COUNTROWS ( INTERSECT ( getLocations, allLocations ) ) + 0

If you want a flag only you can

RETURN
    INT( NOT ISEMPTY ( INTERSECT ( getLocations, allLocations ) ) )