Client-Level Security Context

[Motwi]

Hello everyone,

I have a dynamic DAX measure that shows a client name (or “[REDACTED]”) based on user access defined in the firm security table. The measure works perfectly when the visual’s context is only the client level. However, when adding extra fields from dim_HR (HR person representing the client and the department where this employee is allocated) in the same matrix, the measure’s evaluation context does a full outer join, meaning that now every client is attached to every department.

This is the measure I created:

DisplayClientName =

VAR currentUser =

IF(

     CONTAINSSTRING(USERPRINCIPALNAME(), “Super”),

     it.devteam@companyname,

     USERPRINCIPALNAME()
)

VAR currentClientID = SELECTEDVALUE(dim_Client[clientID])

VAR AccessCount =

    CALCULATE(

        COUNTROWS(SecurityTable),

        SecurityTable [recordNo] = currentClientID,

        SecurityTable [UserEmail] = currentUser

    )

RETURN

    IF(

        ISBLANK(currentClientID),

        BLANK(),

        IF(

            AccessCount > 0,

            SELECTEDVALUE(dim_Client [clientName]),

            "[REDACTED]"

        )

    )

This is my model:

dim_client

clientID	clientName	representative
Cl01	Acme Corp	John
Cl02	Beta LLC	Maria
Cl03	Alpha Tec	Maria

dim_HR

staffID	Staff_name	staffEmail	staff_dept
1	John	john@companyname	UK
2	Maria	maria@companyname	IT

fact_Balances

Rep_ID	clientID	Invoice	Value
1	Cl01	INV0001	1000
1	Cl01	INV0002	1500
2	Cl02	INV0003	500
2	Cl03	INV0004	500

SecurityTable

UserEmail	recordNo
john@companyname	Cl01
maria@companyname	Cl02
maria@companyname	Cl03
it.devteam@companyname	Cl01
it.devteam@companyname	Cl02
it.devteam@companyname	Cl03

When Maria opens the report, this is what she should see on the table

Rep_Dept	representative	clientName	Value
UK	John	[REDACTED]	2500
IT	Maria	Beta LLC	500
IT	Maria	Alpha Tec	500

Can you please help? I've recreated this example in PowerBI, but I cannot attach it. Here are a few screenshots:

Also, do you know why I cannot use this measure on the rows, in a Matrix? if this measure (or similar one) cannot be used in a Matrix, what solutions do you recomend for me to achieve this goal? 

Thank you very much!

Best regards

[v-venuppu]

Hi @Motwi ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @Motwi ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @Motwi ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Regards,

Rama U.

[amitchandak · Accepted solution]

@Motwi , do not check for blank just keep return like

IF(

AccessCount > 0,

SELECTEDVALUE(dim_Client [clientName]),

"[REDACTED]"

)

or first create a measure AccessCount  and then create another measure for if logic.

To use any measure on row/column, you need to do dynamic segmentaion

very similar to

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Power BI ABC Analysis using Window function, Dynamic Segmentation: https://youtu.be/A8mQND2xSR4