topic Re: Username() function as a filter in DAX Commands and Tips

Username() function as a filter

Anonymous — Tue, 12 Jan 2021 07:58:28 GMT

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

Re: Username() function as a filter

amitchandak — Tue, 12 Jan 2021 07:08:12 GMT

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>)

Re: Username() function as a filter

Anonymous — Tue, 12 Jan 2021 07:22:12 GMT

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

Re: Username() function as a filter

amitchandak — Tue, 12 Jan 2021 07:37:57 GMT

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 )
)
)
))

Re: Username() function as a filter

Anonymous — Tue, 12 Jan 2021 08:00:48 GMT

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