Forum Discussion

basirve's avatar
basirve
Helper III
2 years ago
Solved

Dynamic Row level security Overriding My Dax Formula

Dear Experts,
I have agent scorecord report and want to implement Dynamic row level Security.
Hierarchy is : Agent --> Supervisor --> Manger --> Director
When Agent Login and he can able to his data only 
When Supervisor/Manager/Director Logins, We want to show the under his employees.
Below Screenshot is not applied Security:
133--> Total employees at the Same job level.


when row Leve security enabled 
1--> Total employees at the Same job level. 
here we want to show 133 instead 1.


Dax Measure:


_Agent_Counts_Tier =
CALCULATE (
COUNT ( Dim_Agent[_Complete_Name] ),
FILTER (
ALL ( Dim_Agent ),
Dim_Agent[Job_Cd] = SELECTEDVALUE ( Dim_Agent[Job_Cd] )
&& Dim_Agent[is_Current] = TRUE ()
)
)


Can we write a dax to overwrite the row level security in this calculation.
I wrote some kid of measure but it doesnt work as expected.Could you please suggest/help me on this.
_Agent_Counts_Tier_Sec =
VAR EIN_Lookup_Var =
    LOOKUPVALUE (
        Dim_Agent[Job_Cd],
        Dim_Agent[Agent_Federation_ID], USERPRINCIPALNAME ()
    )
Return
CALCULATE (
    COUNT ( Dim_Agent[_Complete_Name] ),
    FILTER (ALL(Dim_Agent) ,PATHCONTAINS(Dim_Agent[Hierarchy_Job_Code],EIN_Lookup_Var))
--Dim_Agent[Job_Cd] = SELECTEDVALUE(Dim_Agent[Job_Cd]) &&
      ,Dim_Agent[is_Current] = TRUE()
           
    )

Thanks & Regards,
Venkat.

 



Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi basirve ,

     

    You can compose a new table of people information as a slicer and create a MEASURE similar to the following:

    result_ = 
VAR sel_ =
    SELECTEDVALUE ( Slier[Person] )
RETURN
    IF (
        sel_ = MAX ( Test[Agent] ),
        [result_Agent],
        IF ( sel_ = MAX ( Test[Manger] ), [result_Manger], BLANK () )
    )

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi basirve ,

     

    You can compose a new table of people information as a slicer and create a MEASURE similar to the following:

    result_ = 
VAR sel_ =
    SELECTEDVALUE ( Slier[Person] )
RETURN
    IF (
        sel_ = MAX ( Test[Agent] ),
        [result_Agent],
        IF ( sel_ = MAX ( Test[Manger] ), [result_Manger], BLANK () )
    )

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • basirve's avatar
    basirve
    Helper III
    2 years ago

    Hi  v-kongfanf-msft,
    We have to compare the Rank too based On the score (It is defined with multiple fact tables.)
    Note: Agent Table connect with Multiple Fact table and when we applied the Row Level security it is filtered the data and for other User are filtered out.
    Thanks & Regards,

    Venkat.