Forum Discussion

emcov's avatar
emcov
Regular Visitor
1 year ago

Multiple data sources and ambiguous table relationships

Hi ! I appreciate any and all advice or suggestions for best practice here.  I have a model that retrieves three tables from an SQL server: RESOURCES[resourceid] > 1:* > EVENTS[eventid, userid, r...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi emcov, 

Try as following:

  • Create calculated column in Users table(Resources do not require authorizations and are accessible to all users):

 

AccessibleResources = 
CONCATENATEX(
    FILTER(
        ADDCOLUMNS(
            Resources,
            "IsAuthorized", 
            IF(
                Resources[RequiresAuthorization] = TRUE(),
                NOT(ISBLANK(
                    LOOKUPVALUE(
                        'SubscriptionUsers'[UserListID],
                        'SubscriptionUsers'[UserName], Users[UserName],
                        'SubscriptionUsers'[SubscriptionResourceName], Resources[ResourceName]
                    )
                )),
                TRUE()
            )
        ),
        [IsAuthorized] = TRUE()
    ),
    Resources[ResourceName],
    ", "
)

 

  • Create columns in Resources table:

 

UserListID = 
LOOKUPVALUE(
    'SubscriptionResources'[UserListID], 
    'SubscriptionResources'[SubscriptionResourceName], Resources[ResourceName]
)
​
RequiresAuthorization = 
IF(
    ISBLANK(Resources[UserListID]),
    FALSE(),  
    TRUE()    
)

 

  • Create column in Event table:

 

IsAuthorizedEvent = 
IF(
    NOT(ISBLANK(LOOKUPVALUE(
        SUBSCRIPTIONUSERS[UserListID], 
        SUBSCRIPTIONUSERS[UserName], RELATED(USERS[UserName]), 
        SUBSCRIPTIONUSERS[SubscriptionResourceName], RELATED(RESOURCES[ResourceName])))),
    "Authorized",
    "Unauthorized"
)​

 

Relationship:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.