Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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?
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...
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):
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).
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.
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-...
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |