USERNAME() function DAX

[Twister8]

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"

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.?

[v-haibl-msft]

@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.

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

[Hance]

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

[Twister8]

@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?

[v-haibl-msft]

@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.

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

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.

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

Best Regards,

Herbert

[Twister8]

@v-haibl-msft tks for answer 

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...? 

[v-haibl-msft]

@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