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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors