Using SELECTEDVALUE in RLS role definition

[fbackes]

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! 🙂

[tex628]

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

[fbackes]

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]

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:

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! 😞

[tex628]

Well something like this:

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

)

[fbackes]

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]

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