Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Username() function as a filter

 

I want to achieve this function.

 

I have a user name column, whenever the user logs in to pbi only his name should be filtered and all other things should show up based on his username alone.

 

How to achieve this?

 

I have already created these 2 measures

 

Viewing As = VAR _USERNAME =CALCULATE(USERNAME()) RETURN RIGHT(
LEFT (
_USERNAME,
SEARCH ( "/", _USERNAME, 1, LEN ( _USERNAME ) )
),
LEN (
LEFT (
_USERNAME,
SEARCH ( "/", _USERNAME, 1, LEN (_USERNAME ) - 16 )
)
)
)
 
//abcdefghijklmnop/rajashri.n will be returned as rajashri.nreturns the user name
 
AltRLS =
IF( selectedvalue(Manager[parent]) = [Viewing As],1,0)
 
//returns the value1 or 0 based on the comparison with the viewing as, if rajashri.n = rajashri.n then 1 else 0
 
I must be able to create AltRls as a calculated column and add that as a page level filter (1), this is not possible in power bi now
 
username() function cannot be used in power bi calculated columns, what is the work around here.
I am not expecting RLS as a solution here.

Please help.

Thanks

this is the error I am getting when trying to add a calculated column to c

Need Help DAX
Need Help DAX measure
  • Anonymous's avatar
    Anonymous
    5 years ago

    I have edited the main message, can you please check and respond accordingly. All tht I am looking for is to be able to check whether the username() returning value and the column value in my table are matching using a calculated column, so that I can add the TRUE value as a page level filter. Hope you got my situation. amitchandak 

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , both username, and userprincipalname can not be used in the column.

     

    Not you can use Functions like Sumx, Countx etc. If needed use values(Table[ID]) // some root level column you can use force this calculation at a lower level.

    Sumx(Values(Table[ID]), <Formula>)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak thanks for the response, can you please elaborate on how sumx or countx could be used in my case. I am unable to understand 

      thanks

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , you doing all operation with username(), how you want use with column ?

        example like

        // I have not tested this

        Viewing As = VAR _USERNAME =CALCULATE(USERNAME()) RETURN
        maxx(values(Table[User]),
        RIGHT(
        LEFT (
        _USERNAME,
        SEARCH ( "/", _USERNAME, 1, LEN ( _USERNAME ) )
        ),
        LEN (
        LEFT (
        _USERNAME,
        SEARCH ( "/", _USERNAME, 1, LEN (_USERNAME ) - 16 )
        )
        )
        ))