Conditionally Filter Specific Visuals Based on Current User Logged In

[WinterMist]

Hello Community - 

If someone could provide guidance, I would appreciate it.

Here is a link to the PBIX:

https://drive.google.com/file/d/1IPk0O0nXhVVXXCOU0yLUtRwYL6RGLtyo/view?usp=sharing

Here is the goal:

SUMMARY

I need a way to:

- conditionally filter only certain visuals for a certain group of users,

- while NOT filtering any other visuals for that same group of users;

- as well as conditionally not filtering at all for other users.

DETAIL

1) CSR Non-Managers should only be able to see their own data in 2 specific visuals:

   - "CSR" Slicer
   - "CSR Summary" Table

2) However, in all other visuals, CSR Non-Managers should be able to see ALL data.

NOTE 1: If the report user is a CSR Manager, or any other role, then no filtering on any visuals should occur.
NOTE 2: RLS is not an option, because all dataset rows need to be available in the other visuals (any visuals aside from the 2).

Simplified model as follows:

'Dim CSR'[IsManager] indicates if the CSR User is a Manager or Non-Manager.

For testing, DAX Measure [01 Current User] is being hard-coded, to simulate the user that is accessing the report.

Scenario 1: User csr1@test.com accesses the report.  (i.e. [01 Current User] = csr1@test.com )

Desired Result: 

- Power BI must verify whether or not the report user is a CSR Non-Manager.

- In this case, CSR 1 is in the list of CSR Non-Managers.  Therefore, certain filtering should take place.

- CSR slicer should be filtered to only show value of logged in user (e.g. "CSR 1").  No other values should appear.

- CSR Summary table should be filtered to only show 1 row of data (e.g. for "CSR 1").  No other rows should appear.

- High-Level Status donut chart should NOT be filtered at all, because it is not one of the specified visuals to be filtered.

Measure [02 Should Current User Be Filtered] = 1, which is correctly saying that filtering should occur because it's CSR 1 (a non-managing CSR).

However, I'm struggling to do the actual filtering itself without breaking something.

Scenario 2: User csr5@test.com accesses the report.  (i.e. [01 Current User] = csr5@test.com )

Desired Result: 

- Power BI must verify whether or not the report user is a CSR Non-Manager.

- In this case, CSR 5 is a CSR Manager.  Therefore, NO filtering should take place (on any visual).

Measure [02 Should Current User Be Filtered] = 0, which is correctly saying that filtering should NOT occur because it's CSR 5.

Scenario 3: Any non-CSR (e.g. Sales Person or other) user accesses the report.

Desired Result:

- Power BI must verify whether or not the report user is a CSR Non-Manager.

- In this case, the user is not even a CSR at all.  Therefore, NO filtering should take place (on any visual).

SUMMARY: 

I need a way to:

- conditionally filter only certain visuals for a certain group of users,

- while NOT filtering any other visuals for that same group of users;

- as well as conditionally not filtering at all for other users.

Regards,

Nathan

[WinterMist]

@johnt75 

My sincere thanks to you!  I was getting nowhere without your help.

Have to study your solution for a while as it's still over my head.

Thank you VERY much!

Nathan

[WinterMist]

@johnt75 

Thank you also for your attempt to resolve this.

Unfortunately, this doesn't work.

Thank you very much for attaching the PBIX.

By replacing VALUES with ALL, within [02 Should Current User Be Filtered], this causes the measure to always return 1, regardless of whether the user is in the list of CSR Non-Managers or not.  This is not right.

- It should return 1 only for CSR Non-Managers (CSR 1, CSR 2, CSR 4).

- It should return 0 for everyone else (CSR 3, CSR 5, or anyone else not in the first group).

However, the measure now incorrectly returns 1 for CSR 3 & CSR 5, when it should return 0.

For managers (CSR 3 & CSR 5), NOTHING should be filtered.  

When Current User = CSR 3 or CSR 5....

- [02 Should Current User Be Filtered] = 1 (but should be 0).

- CSR Slicer should should NOT be filtered, but it is.

- CSR Summary table should NOT be filtered, but it is.

In summary, replacing VALUES with ALL in measure [02 ...] breaks the measure so that it only returns 1, all the time, regardless of the Current User.

NOTE: It is critical that [02 Should Current User Be Filtered] correctly returns:

- 1 IF the current user SHOULD be filtered (AND)

- 0 IF the current user should NOT be filtered.

Let me know if you have any additional suggestions.

Thanks again very much for your time.

Regards,

Nathan

[johnt75 · Accepted solution]

You can't use VALUES on its own, as the CSR table is being filtered by having the CSR name in the slicer or table visual. I think the below works, using REMOVEFILTERS

02 Should Current User Be Filtered = 
VAR CSRFilterEmailList = 
    //ONLY NON-MANAGER CSR'S SHOULD HAVE DATA FILTERED
    CALCULATETABLE(
        VALUES('Dim CSR'[Email]),
        'Dim CSR'[IsManager] = 0,
        REMOVEFILTERS( 'Dim CSR'[Name] )
    )
VAR CurrentUser = [01 Current User]
VAR ShouldCurrentUserBeFiltered =
    //VERIFY IF CURRENT USER LOGGED IN IS A NON-MANAGING CSR.
        //IF YES THEN WE NEED TO FILTER CSR SLICER & CSR TABLE VISUALS BY CURRENT USER LOGGED IN
        //IF NO THEN DO NOTHING.  I.E. DO NOT FILTER THESE VISUALS BY THE CURRENT USER LOGGED IN
    IF(
        CONTAINS(CSRFilterEmailList, 'Dim CSR'[Email], CurrentUser),
        1,
        0
    )
RETURN ShouldCurrentUserBeFiltered

[johnt75]

You can do this with a calculation group and a measure to use as a filter.

First you need to modify your measure

02 Should Current User Be Filtered = 
VAR CSRFilterEmailList = 
    //ONLY NON-MANAGER CSR'S SHOULD HAVE DATA FILTERED
    CALCULATETABLE(
        ALL('Dim CSR'[Email]),
        'Dim CSR'[IsManager] = 0
    )
VAR CurrentUser = [01 Current User]
VAR ShouldCurrentUserBeFiltered =
    //VERIFY IF CURRENT USER LOGGED IN IS A NON-MANAGING CSR.
        //IF YES THEN WE NEED TO FILTER CSR SLICER & CSR TABLE VISUALS BY CURRENT USER LOGGED IN
        //IF NO THEN DO NOTHING.  I.E. DO NOT FILTER THESE VISUALS BY THE CURRENT USER LOGGED IN
    IF(
        CONTAINS(CSRFilterEmailList, 'Dim CSR'[Email], CurrentUser),
        1,
        0
    )
RETURN ShouldCurrentUserBeFiltered

Rather than using VALUES it uses ALL to remove the filters placed by the visuals.

Create a calculation group with a calcultion item

Apply Filter = IF( [02 Should Current User Be Filtered],
    CALCULATE(
        SELECTEDMEASURE(),
        KEEPFILTERS( TREATAS( { [01 Current User] }, 'Dim CSR'[Email] ) )
    ),
    SELECTEDMEASURE()
)

Apply this as a filter to the table visual. It will only show the relevant CSRs.

Create a new measure

Is CSR Visible = IF (
    [02 Should Current User Be Filtered],
    VAR VisibleEntries =
        CALCULATETABLE (
            VALUES ( 'Dim CSR'[Name] ),
            TREATAS ( { [01 Current User] }, 'Dim CSR'[Email] )
        )
    VAR Result =
        IF ( SELECTEDVALUE ( 'Dim CSR'[Name] ) IN VisibleEntries, 1, 0 )
    RETURN
        Result,
    1
)

Add this as a filter to the slicer, to only show when the value is 1.

See the attached PBIX for a working example

[WinterMist]

@speedramps 

Unfortunately, it did not; for the reasons already given in my response above.  I already thanked them for the attempt to help me as well.

The original problem remains unresolved.

Regards,

Nathan 

[speedramps]

@WinterMist Did this answer your question? If so please click [accept solution] and thank the helper or give feedback. Thank you.

[WinterMist]

@v-xianjtan-msft 

Thanks for your recommended solution.

1) Unfortunately, using the CSRID instead of CSR Name within the CSR Summary table would not be acceptable to the business.

- ID's are not helpful to business users and they do not wish to see them in any visual.

- It is critical that the CSR Name is shown in both the slicer & table visuals.

- Do you know if this is possible?

Aside from this significant problem, the results of csr1@test.com look good!

2) Unfortunately, when switching to csr5@test.com (Managing CSR), there is a different problem.

The requirement is that for Managing CSR's, no filtering should take place at all.

However, for csr5, the CSR slicer has been filtered to remove all CSR values.

This would prevent the user (a managing CSR) from using the slicer to select any desired combination of CSR's.

(This is one of the primary goals of the report for managers.)

This is also a significant problem.

Thank you for your time & attempt to help.

Let me know if you have any additional suggestions.

Regards,

Nathan

[v-xianjtan-msft]

Hi @WinterMist 

Please try the following possible solution:

1. Modify your Count measures with the following DAX

(This also applies to [02 Count In Progress] and [03 Count Complete], simply change 'New' to 'In Progress' or 'Complete'. )

01 Count New = 
IF(
    [02 Should Current User Be Filtered] = 1,
    CALCULATE(
        COUNTROWS('Fact Documents'),
        'Fact Documents'[Status] = "New",
        FILTER(
            'Dim CSR',
            'Dim CSR'[Email] = [01 Current User]
        )
    ),
    CALCULATE(
        COUNTROWS('Fact Documents'),
        'Fact Documents'[Status] = "New"
    )
)

2. Create the following measure and drag it into the Filter panel of the CSR filter, setting the filter condition to 'is not blank'.

Filtered CSR = 
IF (
    [02 Should Current User Be Filtered] = 1,
    [01 Current User],
    BLANK()
)

3. Replace the 'Dim CSR'[Name] field in the CSR Summary Table visual with 'Fact Documents'[CSRID].

The test results are as follows:
When user csr1@test.com accesses this report:

When user csr5@test.com accesses this report:

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