Forum Discussion

Roy_tap's avatar
Roy_tap
Icon for Helper I rankHelper I
3 years ago

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

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

First Table contain of User Access with Location (Dim_Access)

UserIDLocation
xxx\abcUS
xxx\abcCA
xxx\bcdAU

 

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

Project IDLocationRevenue 
P1US, CA, AU123 
P2US234 
P3AU345 


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 IDLocationRevenueFlag
P1US, CA, AU1231
P2US2341
P3AU3450


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.

Help

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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 ) ) )