Creating a filter based on USERPRINCIPALNAME

Hi Anonymous 

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 [email protected]:

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