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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
fbackes
Helper I
Helper I

Using SELECTEDVALUE in RLS role definition

I have a special requirement for an RLS role, where the filter of one table depends on the currently selected entity in another table.

I have tried different DAX code in the filter condition of the role with no success:

(HASONEVALUE( DifferentTable[Entity_Key] ) && MAX( DifferentTable[Entity_Key] ) = '"xxx"  && [FieldInFilteredTable] > 100 ) || [FieldInFilteredTable] > 200 

 

(SELECTEDVALUE( DifferentTable[Entity_Key] ) = "xxx" &&  [FieldInFilteredTable] > 100 ) || [FieldInFilteredTable] > 200 

 

The rule is: If the key of the currently selected entity of  "DifferentTable " equals "xxx", all rows of the filtered table with [FieldInFilteredTable] > 100 should be included. Otherwise only rows with [FieldInFilteredTable] > 200.

I fear that the evaluation contexts of the actual report and the RLS data are different.

Any hints would be highly appreciated! 🙂

6 REPLIES 6
tex628
Community Champion
Community Champion

Hello again @fbackes

 

I took a closer look at your initial post and something isn't quite adding up! 🙂

It's not quite clear what the desired outcome is, Is this what you are aiming for?

"Entity key" = "XXX" then "FieldInFilteredTable" > 100 otherwise "FieldInFilteredTable" > 200

 

If that is the case then the correct code should be:

 

 

Measure=
IF(SelectedValue([Entity key]) = "XXX" && [FieldInFilteredTable]>100; "1";
IF([FieldinFilteredTable]>200; "1";
"0"
))

Put this measure in the filter section and filter to only display "1".

I think this should work but if it doesn't we'll work it out! 🙂

Br,
J

 


Connect on LinkedIn

Hello tex628,

 

many thanks again for your reply!

 

>It's not quite clear what the desired outcome is, Is this what you are aiming for?

"Entity key" = "XXX" then "FieldInFilteredTable" > 100 otherwise "FieldInFilteredTable" > 200

>

Exactly! 🙂

>Put this measure in the filter section
This isn't clear to me. Do you mean the filter section of the report or the filter on the respective table in the role definition?

Since this logic must be totally encapsulated and hidden from the user, the filter section of a report is not an option.

 

I also have tried this as the filter condition in the role definition:

IF( SelectedValue([Entity key]) = "XXX" && [FieldInFilteredTable] > 100;
TRUE;
IF( [FieldinFilteredTable] > 200;
TRUE;
FALSE
)
)

 

tex628
Community Champion
Community Champion

Alright, so i've been testing some different things. And honestly I'm not able to solve this...

I'll try and explain why, and what the problem i'm finding is.

This is the result that i have with the measure that was provided:

 

image.png

 

Measure5 = 
IF(SELECTEDVALUE(Table2[Entity])="xxx" && AVERAGE(Table2[Field in filtered column]) > 100;
    1;
    IF( AVERAGE(Table2[Field in filtered column]) > 200;
        1;
        0
       )
)


This appears almost correct, but what's accually happening is that the measure is looking towards the entity column and not the filter. Meaning that if the row value of entity in the table equals "xxx" the measure value becomes 1 even if the filter is not active. 

 

You can see this in the table on the row with "xxx - 110 - 1"
This row should display 0 when the filter is off, and 1 when the filter is on. 

So essentially my issue is that i dont know how to ignore the filter provided by the table visualisation and to have the selectedvalue() analyse the entity column only being filtered by the slicer.

 

I know this might be really messy and hard to understand but this is really difficult to explain, sorry! 😞


Connect on LinkedIn
tex628
Community Champion
Community Champion

Well something like this:

 

Measure = 
IF(SELECTEDVALUE([Entity_key])=XXX);
Calculate(sum([fieldinfilteredtable]); [fieldinfilteredtable]>100);
[fieldinfilteredvalue]

)


Connect on LinkedIn

Many thanks, tex628!

 

Unfortunately, this would not work for me, because the measure is not calculating different results for different roles. 

 

So there are actually three conditions. The filter rule does not only depend on the "DifferentEntity", but also on the role.

 

The condition can be totally different for different roles.

 

Plus, there is no measure involved. It is actually a row based filter on a lookup table.
 

tex628
Community Champion
Community Champion

I'll take a look at this tomorrow morning! 🙂 I'll see you then 


Connect on LinkedIn

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.