topic Re: Measure referring to a column restricted using Column Level Security in DAX Commands and Tips

Measure referring to a column restricted using Column Level Security

hyreddy_016 — Tue, 17 Nov 2020 08:31:53 GMT

I have a use case where I need to evaluate a count based on the column filtered in a report. If the visuals are sliced using A or B, the count should evaluate to 0 else 1.
Following are more details.

I am using the DAX formula , Limit:=IF(OR(ISFILTERED(table[A]),ISFILTERED(table[B])),0,1)

There are two Roles added 1) Read 2) Restricted . Read has access to all the columns. Restricted doesn't have access to columns A and B. In that case I want the limit to be picked up as 1. But currently, the measure it self is getting restricted for the Restricted role.

Is there any way this measure works as per the expected behavior? Any help is highly appreciated. Thanks!

Re: Measure referring to a column restricted using Column Level Security

amitchandak — Tue, 17 Nov 2020 08:44:01 GMT

hyreddy_016 , you need to add condition for role or username in this

Re: Measure referring to a column restricted using Column Level Security

hyreddy_016 — Tue, 17 Nov 2020 10:32:36 GMT

Hi amitchandak , the role member is an azure active directory group and not individual user. Hence I can not add the user related condition in the query. I have added the role name, even then this measure is getting restricted. Could you please provide an example?

Re: Measure referring to a column restricted using Column Level Security

hyreddy_016 — Thu, 19 Nov 2020 07:22:53 GMT

Is there a way we can verify for role membership in a measure using DAX?

Re: Measure referring to a column restricted using Column Level Security

hyreddy_016 — Sat, 17 Apr 2021 15:24:14 GMT

Any leads on this is highly appreciated. Thanks in advance!

Re: Measure referring to a column restricted using Column Level Security

daxer-almighty — Sun, 18 Apr 2021 12:20:47 GMT

Personally, I think you CAN'T have a measure, which uses restricted columns, return a value for a role that does not have access to the columns/tables. But I may be incorrect as I'm not a specialist in this topic. You could try to ask Alberto Ferarri or Marco Russo. Or Chris Webb. They'll know much more on this than I do.

Re: Measure referring to a column restricted using Column Level Security

hyreddy_016 — Mon, 19 Apr 2021 04:42:16 GMT

Thanks daxer-almighty , is there any other way we can acheive this functionality? Without using the restricted columns in the measure directly? Like same using Row Level Security instead of column level security.