Forum Discussion

luzrueda's avatar
luzrueda
Icon for Advocate I rankAdvocate I
24 days ago

Dynamic axis label: real name vs. "Anonymous" per user (RLS)

I have Row Level Security (RLS) set up on my fact table, based on a mapping table (Company ↔ Email). When a user logs in with their email, RLS restricts them to see only rows belonging to their own company.

However, I also have a separate Ranking visual where I want all companies to be visible (for benchmarking purposes), but:

  • The logged-in user's own company should show its real name
  • All other companies should show as "Anonymous" (or similar)

Since RLS filters out other companies' data entirely at the model level, I created a duplicated fact table with no relationship to the RLS table, so all companies remain visible in this ranking view. This part works.

My problem: Since axis fields in a chart must be columns (not measures), I can't directly put an IF(company = current user's company, real name, "Anonymous") measure on the axis.

As a workaround, I tried showing a numeric/letter index/key on the axis, with a small separate table showing "which key = which company" for the logged-in user only. But my client finds this confusing and wants something simpler,  ideally the real company name directly on the axis for their own company, and "Anonymous" for the rest.

Question: Is there a way to dynamically display the correct label (real name vs. "Anonymous") directly on a chart axis, based on USERPRINCIPALNAME(), without using a measure on the axis? Or is there a better pattern for this "self visible, others anonymized" ranking scenario?

I'm using DAX measures like:

'RLS_Firma'[E-Mail] = USERPRINCIPALNAME()

Any guidance on a cleaner approach (calculated column with row-level dynamic logic, field parameters, dynamic format strings, or a different table design) would be greatly appreciated. 

 

1 Reply

  • jubinsoni's avatar
    jubinsoni
    Icon for Continued Contributor rankContinued Contributor
    13 hours ago

    Hi luzrueda​,

    A calculated column cannot do this, because it is evaluated at refresh time and USERPRINCIPALNAME() has no user then. The trick that works is to let RLS choose which row of a label table each user sees, so the axis is still a normal column but its value differs per user.

    1. Build a label table with two rows per company (no relationships):

    CompanyLabel =
VAR Base =
    ADDCOLUMNS (
        VALUES ( Benchmark[CompanyKey] ),
        "@Rank", RANKX ( VALUES ( Benchmark[CompanyKey] ), Benchmark[CompanyKey], , ASC )
    )
RETURN
    UNION (
        SELECTCOLUMNS ( Base, "CompanyKey", [CompanyKey], "LabelType", "Real",
            "Label", LOOKUPVALUE ( Company[CompanyName], Company[CompanyKey], [CompanyKey] ) ),
        SELECTCOLUMNS ( Base, "CompanyKey", [CompanyKey], "LabelType", "Anon",
            "Label", "Company " & FORMAT ( [@Rank], "00" ) )
    )

    Note the anonymous labels must be unique per company (Company 01, 02...). If they were all "Anonymous", the axis would merge them into a single bar.

    2. Add an RLS filter on CompanyLabel in the same role, so each user keeps the real row for their own company and the anonymous row for everyone else:

    VAR MyCompany =
    LOOKUPVALUE ( RLS_Firma[CompanyKey], RLS_Firma[E-Mail], USERPRINCIPALNAME () )
RETURN
    ( CompanyLabel[LabelType] = "Real" && CompanyLabel[CompanyKey] = MyCompany )
        || ( CompanyLabel[LabelType] = "Anon" && CompanyLabel[CompanyKey] <> MyCompany )

    3. Measure for the ranking chart, mapping labels to your unrestricted benchmark table without a relationship:

    Benchmark Value =
CALCULATE (
    [Your Benchmark Measure],
    TREATAS ( VALUES ( CompanyLabel[CompanyKey] ), Benchmark[CompanyKey] )
)

    Put CompanyLabel[Label] on the axis and Benchmark Value in values. Optionally add a colour measure (LabelType = "Real" highlighted) so the user's own bar stands out.

    Testing tip: RLS only applies to Viewers (and in Desktop via View as), so check it with a Viewer account; workspace Admin/Member/Contributor will see both rows per company.

    So my overall approach would be:

    Label table: Real + Anon row per company (unique anon names)
-> RLS on the label table keeps exactly one row per company per user
-> TREATAS measure to the unrestricted benchmark table
-> Label column on the axis, highlight own company by colour
-> Validate with a Viewer account

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.