Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have a table "Email_BE" defining the access privilege. Users with a given email should have access to BUs listed:
I was able to make it work if there is only a single value in BUs:
[BU ID] = LOOKUPVALUE(Email_BE[BUs],Email_BE[UserEmailAdr],USERNAME())
However, I have a problem in getting the proper code to make it work with a list - I want to get the data for all the BUs the user in a member of. I want something like:
[BU ID] in LOOKUPVALUE(Email_BE[BUs],Email_BE[UserEmailAdr],USERNAME())
Any ideas? Many thanks up front! 😄
Solved! Go to Solution.
Hi @Anonymous ,
Try this one.
VAR A = CALCULATETABLE ( VALUES ( Email_BE[BUs] ), FILTER ( Email_BE, Email_BE[UserEmailAdr] = USERNAME () ) ) RETURN [BU ID] IN A
BTW, do mask your Confidential Information in your post. 🙂
Hi @Anonymous ,
Try this one.
VAR A = CALCULATETABLE ( VALUES ( Email_BE[BUs] ), FILTER ( Email_BE, Email_BE[UserEmailAdr] = USERNAME () ) ) RETURN [BU ID] IN A
BTW, do mask your Confidential Information in your post. 🙂
Thanks for help. I did accept the solution, but afterwards I noticed that it works properly, only when a single value is available in BUs. I had to change the type of BU ID to text first, to make "IN" function work. Now, no errors are displayed, but it does not work as desired -if more then one value is present in BUs, than everything gets filtered out and all visualisations are (blank).
Hi @Anonymous ,
Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Thank you! I have prepared a bare-bones version, that can be used for troubleshooting:
There are just 3 different users and some sample data for them.
Hi @Anonymous ,
To use this new one should work 🙂 please have a try.
VAR A = CALCULATE ( CONCATENATEX ( Email_BE, Email_BE[BUs], "" ), FILTER ( Email_BE, Email_BE[UserEmailAdr] = USERNAME () ) ) VAR b = SEARCH ( Org_Div_LoB_BU_Matrix[BU ID], A,, BLANK () ) RETURN b <> BLANK ()
Unfortunately, it does not work. Am I making an error somewhere? Is it necessary to include the other table?
Could you maybe share back the modified pbix file?
Hi @Anonymous ,
In table Mitigations_Initiatives, we should use this formula.
VAR A = CALCULATE ( CONCATENATEX ( Email_BE, Email_BE[BUs], "" ), FILTER ( Email_BE, Email_BE[UserEmailAdr] =USERNAME() ) ) VAR b = SEARCH ( Mitigations_Initiatives[Business Entity ID],A,, BLANK () ) RETURN b <> BLANK ()
In aother one:
VAR A = CALCULATE ( CONCATENATEX ( Email_BE, Email_BE[BUs], "" ), FILTER ( Email_BE, Email_BE[UserEmailAdr] =USERNAME() ) ) VAR b = SEARCH ( Org_Div_LoB_BU_Matrix[BU ID], A,, BLANK () ) RETURN b <> BLANK ()
In your sample file, I user email address instead of username() to check the result. And seemed that to filter Org_Div_LoB_BU_Matrix is enough. Please check the pbix as attached.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |