Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello community! I cannot find much material out there on DAX expressions for RLS. Here is my dilema. I am trying to create a static security view based on sales territories. However, no territories are assigned to Source "A". When I create the role for the territory, people cannot see Source A because there is no territory tied to it. Here is the relationship:
I tried creating this expression, but it is giving me a syntax error.
IF(OR(Revenue Source[Source] = "B", Revenue Source[Source] = "C"),
FILTER(ALL(Territories), Territories[Territory] = "T23"),
ALL(Territories)
)
Spelling is correct.
Essentially, if they are viewing Source B and C, it needs to be filtered to T23. If they are viewing source A, they need to see all the data.
Also, I have to select which table to put this code into, and I am not sure if that matter or not. Do I put it into Revenue Source or do I put it into Territories?
Thanks in advance!
Joe~
Solved! Go to Solution.
Thank you for the quick reply! I tried a few things last night, but I figured out where I was going wrong. I was filtering the role this way:
I was filtering on two tables, and it was giving me the filter ONLY for the revenue source.
I created two roles - one for territory and one for revenue source. Then I gave both roles to the employee and they can see either/or.
The formula you provided I believe is for dynamic RLS, and I want to do static RLS.
Thank you for the quick reply! I tried a few things last night, but I figured out where I was going wrong. I was filtering the role this way:
I was filtering on two tables, and it was giving me the filter ONLY for the revenue source.
I created two roles - one for territory and one for revenue source. Then I gave both roles to the employee and they can see either/or.
The formula you provided I believe is for dynamic RLS, and I want to do static RLS.
HI @jwin2424,
I'd like to suggest you add a custom table to bind username with sources, then you can use current username to lookup correspond source and use it as condition to filter table records.
IF (
LOOKUPVALUE ( 'Table'[Source], 'Table'[email], USERNAME () ) IN { "B", "C" },
Territories[Territory] = "T23",
Territories[Territory] = TRUE ()
)
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |