How to get filtered measure in another table column?

[ksabi]

Hello, 

I'm facing a problem concerning my power BI report. I want to get the connected user name to display a column dynamically. 

If the function USERNAME() was available in calculated column, it would resolve my problem, but unfortunately, it's just an idea for the moment : 
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13926666-provide-username-functio...

Explanation of my problem : 

I have a model in which the fact table is containing to columns (initial sector and calculated sector), related to two dimensions and two roles as mentioned in the picture below : 

 I want to display initial sector OR calculated sector in the report depending to the role of the user. 

For that, i tried some dax calculation, but i can't get the result that i need.

 The measures i created are correct (they display the correct information when visible outside the table) : 

__aa RLS Init = CALCULATE(DISTINCTCOUNT('RLS Init'[Adresse mail]) ) = 1  => the current role with the connected user

__aa RLS GC = CALCULATE(DISTINCTCOUNT('RLS GC'[Adresse mail]) ) = 15

In my fact table, i created a calculated column based on the value of __aa RLS Init to display dynamically the sector : 

__Secteur = if([__aa RLS Init]= 1; 'Fact VE'[Secteur (n) Init]; 'Fact VE'[Secteur (n)]) => It displays the wrong sector! 

To test the behaviour of my measure in the table, i created a calculated column using this code : __aa test Init = CALCULATE(DISTINCTCOUNT('RLS Init'[Adresse mail]) )  = __aa RLS Init (the code of my measure)=> is gives 21 !

I think that when i use a measure in a calculated column, we lose the current (connected user), it takes the whole table!!

some one have an idea to get __aa RLS Init = 1 in my calculated column? 

Thank you in advance for your help.

Best regards,

[ksabi]

 Thank you @v-haibl-msft for your answear. 

I ‘ve already used RLS. 

I used the calculated Sector (only) before => In this case the user who have the right to see the calculated sector get all his data,  but another one who only sees the initial sector get some data lost because we replace his sector (with conditions).

that's why i used RLS with two roles. 

Now, I try the display the correct sector (column) according the users’s profile.

[v-haibl-msft]

@ksabi

Why not use Row-level security (RLS)? It can be used to restrict data access for given users. Filters restrict data at the row level. You can define filters within roles.

Best Regards,
Herbert