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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Twister8
Helper II
Helper II

USERNAME() function DAX

Hello everyone,

 

I'm new in Power BI and I have a doubt:

 

I would like use the Username() function DAX, to filter some datas in Dashboard, like RLS, for example:

 

User DOMAIN\Twister8 can see Country = China

another users can see all Countries

 

IF( Username() = "DOMAIN\Twister8", Country = "China", Country)

 

I wanna use the Username() function, because the anothers users doesnt has Pro License.

 

In my test using the RLS conditions like:

TEST - China [Country] = "China"

 

functionTest.png

When I share the Dashboard with anothers users, the message about requeried Pro License its showed.

So, my question is:

Its possible use alternative, Username() functions, to restrict access on Data for users without use Pro License.?

 

 

6 REPLIES 6
v-haibl-msft
Microsoft Employee
Microsoft Employee

@Twister8

 

I tried to use alternative as you said but have no success. I’ll try to explain my ideas as below though it is proved to be impossible.

I first create another table to store the users which need to be restricted access on Data.

USERNAME() function DAX_1.jpg

 

Then create a measure to get the countries which should be shown for this login user. If the user is Twinster8, then China will be returned. If the user is others, then “” will be returned.

CountryShow = 
VAR Country =
    LOOKUPVALUE ( Table2[Country], Table2[Name], USERNAME () )
RETURN
( IF ( ISBLANK ( Country ), "", Country ) )

 

At last, try to create a calculated table to show the data of specified countries. I though it should be OK now but a "not supported" warning message returned.

("CUSTOMDATA and USERNAME functions are not supported in calculated columns. These functions may only be used in Measures or in the AllowedRowsExpression.")

A related idea can be found here.

FilterTable = 
CALCULATETABLE (
    Table1,
    FILTER ( Table1, SEARCH ( [CountryShow], Table1[Country],, 0 ) > 0 )
)

 

So in my opinion, the only way to do it now is to use RLS with Pro License.

 

Best Regards,

Herbert

bad guys Dev team want more money and require pro license from all users who use this report

@v-haibl-msft First of all... thank you for answer...

 

Maybe this be possible with SSAS like explained this post

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1483cd4f-c23e-40b4-9567-d4420c83e9c2/where-...

 

Somebody has to some else sugestion about this question?

 

@Twister8

 

Let’s try to use this function in measure as below. Not sure if it is the result you wanted.

Assuming we have a simple table like the following one.

USERNAME() function DAX_1.jpg

 

We can create another new table which specific someone can only see the specified country.

USERNAME() function DAX_2.jpg

 

We can create a measure with following formula to show the total sales according to the login user of Power BI service.

TotalSales = 
VAR CountryForUser =
    LOOKUPVALUE ( Table2[Country], Table2[Name], USERNAME () )
RETURN
    (
        IF (
            CountryForUser <> BLANK (),
            CALCULATE (
                SUM ( Table1[Sales] ),
                FILTER ( Table1, Table1[Country] = CountryForUser )
            ),
            CALCULATE ( SUM ( Table1[Sales] ) )
        )
)

When I login to Service with the specified user in Table2, I can only see the total sales of China.

USERNAME() function DAX_3.jpg

 

After I share the dashboard with another user, he can see the total sales of China and USA.

USERNAME() function DAX_4.jpg

 

Best Regards,

Herbert

@v-haibl-msft tks for answer Smiley Happy

 

I am trying do this solution...but i have a problem(In my test, your solution just work when I have the country and sales in the same component, table, if you log in with user that see China and create a table with just country, all coutries are listed, correct?), maybe this solution can be, create various functions DAX for each type column and more tables for combination .....what do you think?

 

for example, I have a dataset with country e sales by year, but i have inside the country some states, some companies, and I need show filters too. The user can see just some countries, some states and some companies:

 

Twiter8 can see, country USA, but some states like TX, NY, FL, and some companies like company A and company F......I am thinking that solution is:

Slicer                               

Country:                State                        Company
USA                       TX                            A

                              NY                           A

                              FL                            F

TotalSales = 
VAR StateForUser =
    LOOKUPVALUE ( Table2[State], Table2[Name], USERNAME () )
RETURN
    (
        IF (
            CountryForUser <> BLANK (),
            CALCULATE (
                SUM ( Table1[Sales] ),
                FILTER ( Table1, Table1[Country] = StateForUser )
            ),
            CALCULATE ( SUM ( Table1[Sales] ) )
        )
)

 I guess the solution because, when I included a slicers, in your example, by state or company, its showing every states and company instead the just show state and company by country and user....

 

What do you think...? 

 

@Twister8

 

If you want to let the login users only see specified country in Slicer, I don’t think it is possible. For fields of slicer, we need to put column into it. So when we login with Twister8, each country in the country column will be displayed but not only USA. We’re not able to filter the column like we did in above measure.

 

Best Regards,

Herbert

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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