Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
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
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
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.
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
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |