Forum Discussion

Twister8's avatar
Twister8
Helper II
10 years ago

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 se...
v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
10 years ago

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

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      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's avatar
        Twister8
        Helper II
        9 years ago

        v-haibl-msft tks for answer :smileyhappy:

         

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

         

  • Hance's avatar
    Hance
    Frequent Visitor
    8 years ago

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