Forum Discussion

rpiboy_1's avatar
rpiboy_1
Icon for Helper V rankHelper V
2 years ago

More efficient approach then 'LookupValue'?

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 wonderi...
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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-Forum/ba-p/963216

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/1447523

  • rpiboy_1's avatar
    rpiboy_1
    Icon for Helper V rankHelper V
    2 years ago

    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.