Security role with more values

[rode]

Hi everyone.

I'm getting in touch with the roles in Power BI. I've seen that there is a basic way to filter some tables basing on the username. Nevertheless, I've not found a simple pattern to address the need to filter out basing on many values. Let's see an example.

I have a fact table, called 'Fact', which is the one I want to filter out basing on some values. This table has field called Customer, which has values "A","B","C" and so on.

I've also created a table called 'Security' that has two fields: Username (which assumes the form ORGANIZATION\NAME) and SecCustomer. A user could be able to see more than one customer and for this reasone the table 'Security' contains many rows for each username in order to address this need.

I woul like to write a DAX formula in a role specificated for 'Fact' table that should have a behaviour like the following one:

[Customer] = LOOKUPVALUE('Security'[SecCustomer], 'Security'[Username], USERNAME())

The problem is that LOOKUPVALUE function returns only one value and does not suit at all this situation.

How can I do to filter out 'Fact' table basing on the Customers that come out from the security table in accordance to the given username?

Thanks.

[v-caliao-msft]

Hi @rode,

You said that
"A user could be able to see more than one customer and for this reasone the table 'Security' contains many rows for each username in order to address this need."
Why not create a calculated column in Fact table
UserName = LOOKUPVALUE(Security[Username],Security[SecCustomer],'Fact'[Customer])

and then set role on Fact table.

Regards,

Charlie Liao

[rode · Accepted solution]

---

@v-caliao-msft wrote:

Hi @rode,

You said that
"A user could be able to see more than one customer and for this reasone the table 'Security' contains many rows for each username in order to address this need."
Why not create a calculated column in Fact table
UserName = LOOKUPVALUE(Security[Username],Security[SecCustomer],'Fact'[Customer])

and then set role on Fact table.

Regards,

Charlie Liao

---

Hi @v-caliao-msft and thank you for your reply.

I have a fact table with too many rows and I think it's not a good solution for the performance perspective. However, this solution lets the user see the calculated column, and that's not so conceptually right sinche the fact this is a security information.

In any case, I've implemented a solution that suits well. Up to now I've ignored the context of roles, which is by row (please, correct me if I'm wrong). So, considering this, iht eonly thing to do is to write the DAX query in the role which will filter by Customer and by Username too, such that the lookup can return only one row:

NOT(ISBLANK(LOOKUPVALUE(
'Security'[SecCustomer],
'Security'[SecCustomer], 'Fact'[Customer],
'Security'[Username], USERNAME()
)))