View Slicer information measure, not working when implemented with "RLS"

[vnanduri]

Hi Team,

I have built a measure which displays my slicer selection at any given time. For this example, I have chosen Australian cities as my slicer options.

What the measure does is  a. when no city is selected, it displays a value that data is showing for "All Cities" and b. when one or more (but not all) cities are selected, it shows the list of all the cities which are selected.

a.  

b.

The measure that I have used is 

Measure = IF(ISFILTERED(Table_Name[Column_Name]), CONCATENATEX( VALUES(Table_Name[Column_Name]), [Column_Name], ", "), "All Cities")


It works fantastic under normal conditions but not when RLS is implemented.

For example, if I create a role which has the ability to view records specific to "Adelaide" and I view the report as that specific role, my measure still displays the slicer choice as "All Cities". (refer to screen shots below)

How do I get around this? My objective is when a user who belongs to the security group "Adelaide" logs in to view the report, he/she should automatically view the information on the Card "Data Showing for the City" as "Adelaide" .

It would be awesome if someone could help me out with this!

Thanks,

VJ

[v-kelly-msft]

Hi @vnanduri ，

It is because RLS is applied in the data model,so when you are using RLS,the column is being filtered as set in RLS,but in your measure ,only when the data is selected in the slicer,then it will show as the data in the slicer,that's why it is showing "all cities".(As none value is selected currently)

You need to modify your measure as below:

Measure = 
var a =DISTINCTCOUNT('Table'[Column1])
Return
IF(a=1,MAX('Table'[Column1]),IF(ISFILTERED('Table'[Column1]), CONCATENATEX( VALUES('Table'[Column1]),[Column1], ", "), "All Cities"))

Then you will see:

Check the attachment for details.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[vnanduri]

Hi Kelly, (@v-kelly-msft ) 

Thank you for this. This works perfect for me.

One additional thing I was wondering is how do I get the Card visual to display information if the RLS role has more than one access.

For example, in the file that you have provided me, "New Role" has the ability to view info for only "a". If the role allows me to view information for "a" as well as "q", the measure displays "All Cities". What I want is it to display "a, q".

Is this possible?

Thanks,
Vijay

[v-kelly-msft · Accepted solution]

Hi @vnanduri,

Sorry for the late reply.

Yes,it is available,see steps below:

First,create a calculated column to the total number for distinct value in column 1:

Distinctcount total = CALCULATE(DISTINCTCOUNT('Table'[Column1]),ALL('Table'))

Then create a measure as below:

Measure = 
var a =DISTINCTCOUNT('Table'[Column1])
Return
IF(a=1,MAX('Table'[Column1]),IF(a>1&&a<MAX('Table'[Distinctcount total]) ||ISFILTERED('Table'[Column1]), CONCATENATEX( VALUES('Table'[Column1]),[Column1], ", "), "All Cities"))

And you will see:

Pls see attached for details.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[vnanduri]

Hi Kelly, @v-kelly-msft 

That did its magic. Thank you so much for your help.

Cheers,

VJ

[amitchandak]

@vnanduri , not sure I got it

City = if(ISFILTERED(Geography[City]),CONCATENATEX(distinct(Geography[City]) ,Geography[City] &" "),"All")

City = if(ISFILTERED(Geography[City]),max(Geography[City] ),"All")