Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ruthwiksai_2021
Frequent Visitor

Dax code for Row level security

I have a cube in SSAS.in the cube i have a single table. I have category and MCO columns are there.in the category i have different product values are like "tea,coffe and MCO different values are there like in below i attached so i created a role. in the role i dont want show tea related some of MCO values when user login in service.I want show for tea category- MCO(india,indenosia,nepal countries). Is this possiable to create RLS?

=IF(SELECTEDVALUE(BPPlusReport[Category])="Tea",ISFILTERED(BPPlusReport[MCO]) in {"India","Nepal","Indonesia"}) I tried this but it is not working.

Ruthwiksai_2021_0-1641787196057.png

 

 

1 ACCEPTED SOLUTION

Hi again @Ruthwiksai_2021 

The initial equals sign isn't needed when entering the Table filter DAX expression in Power BI Desktop.

If you remove the equals sign does it work?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
Ruthwiksai_2021
Frequent Visitor

Hi Owen,

 

But  I have multiple fact tables are in modeling and have realtionship between dim tables and fact tables. I am using slicers category and MCO columns are from dim tables only. Is i need create the category and MCO column are in fact tables using RELATED function.

 

Thanks

Ruthwik

Hi again Ruthwik,

 

If your fact tables are all related to Category & MCO dim tables, I would say it's best to avoid replicating those columns in the fact tables.

 

Rather, within the role, I would suggest you create a RLS table filter expression repeated for each fact table, using RELATED function.

I think it would look something like this:

 

OR (
    RELATED ( DimCategory[Category] ) <> "Tea",
    RELATED ( DimMCO[MCO] ) IN { "India", "Nepal", "Indonesia" }
)

 

 Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

OR(BPPlusReport[Category]<> "Tea",BPPlusReport[MCO] IN { "India", "Nepal", "Indonesia" }) this dax one is working in Power BI desktop but when i used this in Azure anlysis service (role)dax executed but when doing validate the role, it is not showing expected. Is there any limitation for above in AAS.

 

Thanks

Ruthwik

OwenAuger
Super User
Super User

Hi @Ruthwiksai_2021 

 

With RLS, the DAX expression is evaluated in the row context of the relevant table.

For this reason, you can directly reference columns, with no need for SELECTEDVALUE or ISFILTERED.

 

A concise way of stating your condition is this (hopefully I understood correctly):

OR (
    BPPlusReport[Category] <> "Tea",
    BPPlusReport[MCO] IN { "India", "Nepal", "Indonesia" }
)

This would include all non-Tea, plus Tea only for the three MCO listed.

 

If you like, you can test this out by creating a calculated column with the same expression, filtering it to TRUE, and checking if the expected rows are visible.

 

Let me know if this works.

 

Regards,

Owen

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi Owen

 

it is working when i created the column and filtered for tea but when I applied same DAX in Roles it is showing syntax error. Please find below screen shot.

Ruthwiksai_2021_0-1641789546134.png

Thanks

Ruthwik

Hi again @Ruthwiksai_2021 

The initial equals sign isn't needed when entering the Table filter DAX expression in Power BI Desktop.

If you remove the equals sign does it work?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi Owen,

I am also facing the same issue.
In my case 
I have different OU's(North America, Latin America,Asia etc.)
and Quarterly data as Q1, Q2, Q3 and Q4.

So, I want to hide Q4 data for Latin America OU.
How Can do that with DAX.

Also, OU's and Quarter are in different tables.
where should I write the measure and What should I write.

Thanks In advance

 

Hi Owen 

 

it is working now. Thanks for quick respones and One more doubt  I have one more  cube is there. In that cube dim category and dim mco tables are there. . we dont have realtionship between dim category and dim mco tables. can we achieve same thing. what is DAX code can we write for that secenario.

 

Thanks

Ruthwik

You're welcome 🙂
If you need the same filter applied in a dataset where Dim Category & Dim MCO are separate tables, you would have to apply the filter in the fact table, which I presume is related to both Dim tables. There is no way that the filter could be applied on the Dim tables themselves.

 

The code may be a little different, in that you may need to use the RELATED function to access the relevant columns of those tables.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors