More efficient approach then 'LookupValue'?

[rpiboy_1]

I have the following measure written. I'm using LOOKUPVALUE twice to transit two relationships back to the column value I'd like to concatentate together into a string to show in a card. Just wondering if there is a more efficient approach. The relationships are 1:* from 'User List' -> 'User List Regional' -> 'User Activity Log'.

Most Recent Logins = 
VAR _multival = HASONEVALUE('Projects'[region.project_id])
VAR _result =
    IF(
        _multival,
        CONCATENATEX(
            CALCULATETABLE(
                VALUES('User Activity Log'[UsersRegional.region.userId]),
                LASTDATE('User Activity Log'[login_date])
            ),
            LOOKUPVALUE( 'User List'[Username], 'User List'[id], 
                LOOKUPVALUE('User List Regional'[Id], 'User List Regional'[region.userId], 'User Activity Log'[UsersRegional.region.userId])
            ),
            //'User Activity Log'[UsersRegional.region.userId],
            ", "
        ),
        "This list will only populate when a single project is selected."
    )

RETURN
    _result

[rpiboy_1]

Sample file with a simple model and example of current DAX Measure.

[rpiboy_1]

Worth noting, the current functional measure also gets the list of users based on the last date that there were logins, not all users that have logged in historically. This is another important part of the bahavior.

In the vernacular I want to transfer the filter context of the Fact Table (User Activity) to the User List table, but that requires traversing two relationships. Maybe I just need to further modify the 'calculatetable' statement I already have?

[lbendlin]

Best approach is to use the data model (via RELATED).  Second best is TREATAS.  A far distant third would be LOOKUPVALUE.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[rpiboy_1]

I have a fact table called

'User Activity Log',

Related to the fact table are two dimension tables (all relationships are 1:* from the Dimensions to the Fact tables):

• 'Projects'
• 'User List Regional'
  • related to 'User List Regional' is another dimension table:
    • 'User List'.
      • User List regional is hidden in the data model and acts as a 'translation' between another Regional dimension table and 'User List' which is global in scope.

When the measure is implemented the result should be a list of Users from 'User List' based on the context in 'User Activity Log' being a single project (otherwise the measure returns a text value that says as much).

1. The context of a single 'Project' (dimension) filters the 'Actvity Log' (fact) to a list of users
2. Return the columm 'User List'[Username] (dimension via 'User List Regional')
3. Concatenate the values in the column 'User List'[Username].

The measure works fine if I return the 'Activity Log'[regional.user.id] however ID values are not particularly useful to the report consumers. The current iteration using LOOKUPVALUE also works, I just know its not terribly performant and would prefer to understand how to implement either RELATED or TREATAS. It seems likely that TREATAS may be the best/easiest bet, but again, I'm getting lost in trying to understand the appropriate implementation.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...