Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Roy_tap
Helper I
Helper I

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.

1 REPLY 1
tamerj1
Super User
Super User

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

1.png

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.