Creating a filter based on USERPRINCIPALNAME

[Duygu_Gnd]

Hi everyone,

I have following user table and I have applied Row Level Security based on Organisational Hierarchy. I would like to create a slicer based on logged in user as if logged in person's state is same as user's state then "Domestic" else "International". However, I can't use USERPRINCIPALNAME in calculated columns/tables. so any idea if that's doable? I have attached .pbix file as well. thanks

PATHCONTAINS(Users[Path_Func],LOOKUPVALUE(Users[user ID],Users[email],USERPRINCIPALNAME()))

Formula not working beacuse we can't use USERPRINCIPALNAME in calculated columns/tables.

Domestic/Interstate = IF(Users[user State]=LOOKUPVALUE(Users[user State],Users[email],USERPRINCIPALNAME()),"Domestic","Interstate")

[OwenAuger · Accepted solution]

Hi @Duygu_Gnd 

As you've observed, USERPRINCIPALNAME() cannot be used in calculated columns. This is because calculated columns have to be processed (and therefore fixed in value) before any individual user queries the dataset and RLS is applied.

So any possible solution would have to involve either:

1. Additional RLS filters
2. Measures (which could be extended to Calculation Groups)

I couldn't see your attached PBIX, but I have attached a PBIX with a couple of ideas using these two options.

1. Addional RLS filters

You could create an additional State table that contains every possible state along with both possible values of Domestic/Interstate:

The State column of this table is then related either directly or indirectly (via bridge table) to the Users[user State] column.

Then, to ensure each State is correctly classified when a particular user queries the dataset, create an RLS filter on the State table, that "selects" the correct classification for each State based on USERPRINCIPALNAME().

VAR CurrentUserState =
    LOOKUPVALUE (
        Users[user State],
        Users[email],
        USERPRINCIPALNAME()
    )
RETURN
    IF (
        'State'[State] = CurrentUserState,
        'State'[Domestic/Interstate] = "Domestic",
        'State'[Domestic/Interstate] = "Interstate"
    )

You can then filter on the column State[Domestic/Interstate], which will propogate to the Users table.

2. Measures (which could be extended to Calculation Groups)

You could alternatively create a segmentation table called Domestic/Interstate Segment containing just Domestic/Interstate, and create a Calculation Group which applies segmentation to any measure.

First create Domestic/Interstate Segmentation

Then create a Calculation Group in Tabular Editor with a single Calculation Item that applies the segmentation
(I created the Calculation Group 'Segmentation Calculation Group'[Segmentation Rule] with single Calculation Item "Domestic/Interstate Segmentation".):

VAR CurrentUserState =
    LOOKUPVALUE (
        Users[user State],
        Users[email],
        USERPRINCIPALNAME()
    )
VAR StateFilter =
    GENERATE (
        VALUES ( 'Domestic/Interstate Segment'[Domestic/Interstate] ),
        FILTER (
            VALUES ( Users[user State] ),
            IF (
                Users[user State] = CurrentUserState    ,
                'Domestic/Interstate Segment'[Domestic/Interstate] = "Domestic",
                'Domestic/Interstate Segment'[Domestic/Interstate] = "Interstate"
            )
        )
    )
                
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( StateFilter )
    )

Then in the report, apply the single Calculation Item as a filter, and use Domestic/Interstate Segment[Domestic/Interstate] to filter/group in visuals.

Result

As long as USERPRINCIPALNAME() corresponds to one of the users listed, and an appropriate measure is used in visuals, then both methods give expected results. For example, if you view as user1@xyz.com:

If for some reason RLS didn't apply (such as the report viewer having higher than Viewer permissions in the Workspace), the first method could produce odd results, and might require a bit more thought.

Well, those are some ideas anyway, and there are undoubtedly variations of these that would also work 🙂

Do either of those methods work in your model?

Regards,

Owen

[marion1]

Thank you so much @OwenAuger for taking the time to write such detailed explanations.

I've used solution n.2, it works beautifully. 

[Duygu_Gnd]

Hi @OwenAuger ,

I really appreciate your time and help on that query. I have applied solution number-1 to my actual model and it is working as expected. I haven't had a chance to apply second solution yet, but will definetely try that one as well. 

thanks again

Great response!

Duygu

[amitchandak]

@Duygu_Gnd , You can create a measure as only one value would be there for use login(userprincipalname )