Filter using USERPRINCIPALNAME()

[Anonymous]

Hi folks

Another day another challenge 😉

We have RLS in all our reports but are trying to find ways to open up the datasets whilst still showing the "relevant" data for the user. RLS is set up based on a location table that uses filtering from a User / Location List and the USERPRINCIPALNAME() function.

The ideal scenario would be that a list of locations assigned to the user becomes visible in a slicer. By default, I would set the value to only show those locations whilst enabling the user to also see other locations e.g. through bookmarks.

Hence, I need to make a calculated table. Here is what I got:

 I created a simple model:

Simple model

In the StationUser Table I have:

upn contains email addresses, sry for the blank out

Now I can create a measure to count how many stations I am assigned to:

Count My Stations =

CALCULATE (
    COUNTROWS ( Stations );
    StationUser[upn] = USERPRINCIPALNAME ();
    CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH )
)

And it will return the correct count. So this works.

I can do the same with the calculated table by stating my UPN explicitly in the formula 

My Stations =
CALCULATETABLE (
    VALUES ( Stations[station] );
    StationUser[upn] = "myemail@email.com";
    CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH )
)

BUT, when I use the Prinicpalname() instead of the "myemail@email.com" in a calculated table, it won't let me:


It seems that it is not possible to create a dynamic table based on the USERPRINCIPLENAME () function?

Does anyone have a solution/alternative for this?

Thanks!

[v-juanli-msft]

Hi @Anonymous

How about using this formula

Measure:

USERPRINCIPALNAME = USERPRINCIPALNAME()

My Stations =
CALCULATETABLE (
    VALUES ( Stations[station] );
    StationUser[upn] = [USERPRINCIPALNAME];
    CROSSFILTER ( Stations[stationid]; StationUser[stationid]; BOTH )
)

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft
Thanks for the help. I tried something similar. With your formula, it still returns an error saying: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I guess that's really a challenge and is more complex than I thought it would be.

[amarines]

Hi 

CROSSFILTER is not allowed here, write the next

My Stations =

CALCULATETABLE(
    VALUES(Stations[station] )
    ;FILTER(
        VALUES(Stations[station])
        ;CALCULATE(
            COUNTROWS(StationUser)
            ;StationUser[upn] = [USERPRINCIPALNAME]
        )>0
    )
)

[v-juanli-msft]

Hi @Anonymous

Yes, it is not possible to create a dynamic table based on the USERPRINCIPLENAME () function, you could use the fuction in a measure instead.

Here is a similar thread.

Implementing RLS

Best reagrds

maggie

[Anonymous]

Hi @v-juanli-msft

Thanks for the link, it looks very similiar and I got a step closer. I now have a measure that can tell me whether the current selection is assigned to me or not:

CurrentUPN = USERPRINCIPALNAME()


----AND----


IsCurrentUPN = 
IF(HASONEFILTER(Stations[station]);
IF (
    CONTAINS (StationUser;StationUser[upn];[CurrentUPN]);
    TRUE();
    FALSE()
))

But since it is a measure, I can't use it in a slicer. I need to link it to a table somehow.

Is there a workaround?

Best regards

[Anonymous]

Can you find the solution to this? if yes, share with me also please