Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |