Filter Table by Username Without Row Level Security

[Shawn_Eary]

I have a table named studentSuccess that looks something like this (Data is Not Actual):

Id	rowUserName	Created	min GPA	graduated	Not Graduated	currentUserName (Measure)
6	jeffATfake.zzz	3/2/2020  10:20	2.4	100	100	shawnATfake.zzz
5	shawnATfake.zzz	3/2/2020  8:20	3.9	195	5	shawnATfake.zzz
4	jdoeATfake.zzz	3/2/2020 7:20	0.2	20	180	shawnATfake.zzz
3	sdowATfake.zzz	2/18/2020 15:23	1.8	60	140	shawnATfake.zzz
2	shawnATfake.zzz	2/11/2020 16:50	1.1	40	160	shawnATfake.zzz
1	jeffATfake.zzz	2/10/2020 16:50	3.8	190	10	shawnATfake.zzz

All colums correspond to a SharePoint List except for the currentUserName column which is a Measure that is set via the following DAX code:

currentUserName = USERPRINCIPALNAME()

I am able to display the table in various Power BI visualizations, but I want to filter them so the current user only sees the rows that match her/his username. I do not want to use Row Level Security for this.

In the example above, shawnATfake.zzz is the currently logged in user; therefore, his visualizations should only display rows with Id values of 6, 5 and 2.

To achieve this, I thought I could create a new measure named IsCurrentUser for my studentSuccess table with the following DAX code:

IsCurrentUser = 'studentSuccess'[rowUserName] = USERPRINCIPALNAME()

I was then going to set a static filter on all visualizations to only display the row when IsCurrentUser is True.

Unfortunately, when I try to create the above measure, I get an error:
"A single value for column 'rowUserName' in table 'studentSuccess' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

So then I tried to create a calculated column with this DAX code:
isCurrentUser2 = 'studentSuccess'[rowUserName] = USERPRINCIPALNAME()

When I try that, I get a different error:

CUSTOMDATA, USERNAME, USERCULTURE and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

So either way I go, I get an error.  I want to filter the results to only the current user without using Row Level Security (RSL).  Is there an easy way to do that?

[Shawn_Eary · Accepted solution]

I found the answer here:
https://community.powerbi.com/t5/Desktop/Error-on-using-UserName-function-in-custom-table/m-p/344243...
v-jiascu-msft posted the answer.  I just had a hard time understanding what v-jiascu-msft was saying. 

Apparently, I should not try to use a calculated column, but instead use a measure.  When I use a measure, however, I have to use a MIN or MAX function in the way that Dale (v-jiascu-msft) suggests.  

I would argue that for situations like these where we want to avoid naked columns, we should maybe also have an IDENTITY function to go along with MIN and MAX.  Maybe we could use the IDENTITY function whenever either MAX or MIN would work and it doesn't matter.  Seems like using the IDENTITY function in those situations might make the code easier to read.

DAX serious confuses me.

[Syndicate_Admin]

Hello.

Did you find a solution? I have also tried to use the USERPRINCIPALNAME() to filter information that can be seen by the logged-in user and all the options I have tried failed.

It would be good if they enabled a solution for this, since there are many of us who need to filter by user without RSL.

[cv77]

Shawn, can you tell me how you set your table column to contain USERPRINCIPALNAME() ?  When I try to do that I get a message that the UPN can't be used in a column, only in a measure.  Same as you, I have the requirement to filter data using username without using RLS, and am trying to figure out how to do that.  I can put the UPN (or username()) in a measure, but can't filter my report pages using a measure.  I think that if I could reference the username in a column I can compare the current user with our usertable, which in turn contains IDs which are used to filter subsequent data on the reports.  

Thank you for any suggestions. 

[Shawn_Eary]

---

@cv77 wrote:

Shawn, can you tell me how you set your table column to contain USERPRINCIPALNAME() ?  When I try to do that I get a message that the UPN can't be used in a column, only in a measure. 

---

@cv77 - I'm very sorry.  I wasn't able to look at this last night.  I think you are right.  I am only able to use the USERPRINCIPALNAME() function inside a Measure.  It doesn't work for me in a Calculated Column.  I wanted it to work in a Calculated Column, but I could never get that to happen, so I wound up using a very convoluted "half-broken" workaround that employed Measures instead of Calculated Columns.

I'm sorry I don't remember the details on this.  I've since worked on other projects.

[cv77]

Got it, thank you for your response Shawn.

[v-shex-msft · Accepted solution]

Hi @Shawn_Eary,

The measure is an expression used to calculate based on corresponding row content. Its result will changes if row contents change.
You can't direct use table or columns in its expression except you enable aggregate functions or filters to the summary result value. (it calculate on filtered summarize values instead direct calculate on table fields)

For the calculated column, it is a field stored on table so its calculate range is the whole table and requires calculating the result when the data model table updated. (BTW, you can directly use column fields to calculate in its expression)

You can take a look at the following link to know more about the difference between the calculated column and measure:

Calculated Columns and Measures in DAX 

Username and other functions listed in the notification messages, they are extracted from AS tabular instance features/information. For these special functions, I think they are limited to use on minimum 'row' level(might be security reasons) and not allow to use in column and table level.

Regards,

Xiaoxin Sheng

[Shawn_Eary · Accepted solution]

I found the answer here:
https://community.powerbi.com/t5/Desktop/Error-on-using-UserName-function-in-custom-table/m-p/344243...
v-jiascu-msft posted the answer.  I just had a hard time understanding what v-jiascu-msft was saying. 

Apparently, I should not try to use a calculated column, but instead use a measure.  When I use a measure, however, I have to use a MIN or MAX function in the way that Dale (v-jiascu-msft) suggests.  

I would argue that for situations like these where we want to avoid naked columns, we should maybe also have an IDENTITY function to go along with MIN and MAX.  Maybe we could use the IDENTITY function whenever either MAX or MIN would work and it doesn't matter.  Seems like using the IDENTITY function in those situations might make the code easier to read.

DAX serious confuses me.

[Shawn_Eary]

---

@Shawn_Eary wrote:

I found the answer here:
https://community.powerbi.com/t5/Desktop/Error-on-using-UserName-function-in-custom-table/m-p/344243...
v-jiascu-msft posted the answer.  I just had a hard time understanding what v-jiascu-msft was saying.

---

This trick is working for me when I use the Stacked Column chart visualization, but strangely, I can't use the Pie Chart visualization because when I add the isCurrentUser Measure to my Pie Chart visualization, the "Show items when the value:" DropDowns are strangely disabled.  That isn't a big deal though because I can simply use the Stacked Column chart since it essentially displays the same information when I set rowUsername as the axis and filter to only select rows when isCurrentUser = 1.  Unfortunately, I have a different issue now...

When I filter my Stacked Column chart to only display results when (isCurrentUser = 1), I get exactly one Stacked Bar that correponds to the current user.  This is close to what I want, unfortunately, that Stacked Bar displays the summation of all graduated and notGraduated values for that current user.  I don't want the summation, I simply want the latest value for the current user.  To achieve that, I try to add a Top N Latest 1 query on the Created Field.  Unfortunately, as soon as I apply that, my Stacked Bar chart dissappears because (in my particular case) the current user is not the the user that last submitted an entry to the studentSucess Table.

I need my (Top N Lastest 1) and (CurrentUser is 1) filters to work together so the Stacked column chart shows data from the latest row for the current user regardless wheither or not the current user is the one who created the latest entry.

[Shawn_Eary]

I came close to getting a DAX forumula to resolve this but I kept getting hiccups.  My present resolution is to simply use a UNIQUE constraint to only allow one row per user until I am more familiar with Power BI.