Security role with more values

---

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()
)))