Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Duygu_Gnd
Regular Visitor

Creating a filter based on USERPRINCIPALNAME

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

Users.PNG

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

RowLevelSecurity.PNG

 

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")

error.PNG

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:

OwenAuger_0-1649413918293.png

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

OwenAuger_2-1649414073728.png

 

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

OwenAuger_3-1649414281696.png

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 )
    )

 

 

OwenAuger_4-1649414378464.png

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:

OwenAuger_7-1649414854576.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

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:

OwenAuger_0-1649413918293.png

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

OwenAuger_2-1649414073728.png

 

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

OwenAuger_3-1649414281696.png

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 )
    )

 

 

OwenAuger_4-1649414378464.png

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:

OwenAuger_7-1649414854576.png

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

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

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
Super User
Super User

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors