Use conditional formating to set font to transparent

[JERN]

My goal is to create a sales report where each sales team member can see the Total Sales measure for each sales team member, but the only "employee_last" field they will be able to see is their own. Assume I can derive the report viewer's email address from userprincipalname().  For example:

table 1 is the user table

employee_last	employee_email	employee_id
Adams	aadams@acme.com	1
Jones	ajones@acme.com	2
Smith	asmith@acme.com	3
Thompson	athompson@acme.com	4

table 2 is the sales table.

employee_id	sale_amount
1	5
1	7
1	16
2	8
2	3
2	9
3	6
3	4
3	2
4	8
4	9
4	3

Table 3 is the full result table.

employee_last	Total Sales
Adams	28
Jones	20
Smith	12
Thompson	20

Let's say employee Smith is looking at the report, what I want them to see is: 

employee_last	Total Sales
	28
	20
Smith	12
	20

This way they can see all of the sales data but can only put a name with their own results.  I've tapped out my knowledge on conditional formatting and I'm really not looking for RLS.  Any help would be appreciated.

[JERN]

Barthel, if you still have it could you post what your table 3 contains.  I've been semi-successful but I am getting a 1 to 1 relationship connecting table 1 and 3.

[Barthel]

Hey @JERN,

Sure glad to help. Table 3 is a combination of Table 1 and Table 2. You start with Table 1 and add Table 2 as a calculated column. You then expand this, so you end up with the combination between table 1 and table 2. You do this because you want to show all sales for each employee, with only his own sales being personally visible and the rest anonymized. To achieve this, the following transformations are performed, where a merge takes place and another calculated column is added. In the end, the sales are aggregated per employee so that you have the total sales. 

This is how your table should look like:

total_salesemployee_idemployee_last

28	1	Adams
20	1	uHuUUfaF
12	1	ZOCusvKO
20	1	ucRQs2Ty
28	2	aLxVVBRT
20	2	Jones
12	2	ZOCusvKO
20	2	ucRQs2Ty
28	3	aLxVVBRT
20	3	uHuUUfaF
12	3	Smith
20	3	ucRQs2Ty
28	4	aLxVVBRT
20	4	uHuUUfaF
12	4	ZOCusvKO
20	4	Thompson

[Barthel · Accepted solution]

Hey @JERN 

To achieve this you will have to use an RLS, by indeed using the userprincipalname() function. Create a role (e.g. 'RLS') and apply the following rule to the empolye email column in table 1.

The row level security filters table 1 based on which account (email) the report is viewed with. After filtering, there should be one row left in table 1 (with the email address of the person viewing the report), with wich we can generate a specific view for that person.

Working with transparent colors can be risky: if you hover over the table, the names are still visible in the tooltip and if you export the data you can also trace the names. I believe it's more safe to anonymize the data itself. First you assign an anonymous label to an employee in table 1. You can use this query code (a new anonymous label is created every time you refresh the data):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJzC1W0lFKTAQxHBKTc1P1kvNzgSKGSrE60Upe+XmpYPksEANZ3ggsH5ybWZIBki8GMZDljcHyIRn5uQXF+XkgJSVQNrIqE6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [employee_last = _t, employee_email = _t, employee_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"employee_last", type text}, {"employee_email", type text}, {"employee_id", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "anonymize", each let 
    StringLength = 8,
    ValidCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456879",
    fnRandomCharacter = (text) => Text.Range(ValidCharacters,Int32.From(Number.RandomBetween(0, Text.Length(ValidCharacters)-1)),1),
    GenerateList = List.Generate(()=> [Counter=0, Character=fnRandomCharacter(ValidCharacters)],
                   each [Counter] < StringLength,
                   each [Counter=[Counter]+1, Character=fnRandomCharacter(ValidCharacters)],
                   each [Character]),
    RandomString = List.Accumulate(GenerateList, "", (a,b) => a & b)
in
    RandomString),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"anonymize", type text}}),
    Custom1 = Table.Buffer(#"Changed Type1")
in
    Custom1

Then you want to compile table 3 in such a way that the personal and anonymous data are combined. Use this code for this:

let
    Source = table1,
    #"Added Custom" = Table.AddColumn(Source, "table2", each table2),
    #"Expanded table2" = Table.ExpandTableColumn(#"Added Custom", "table2", {"employee_id", "sale_amount"}, {"table2.employee_id", "table2.sale_amount"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded table2", {"table2.employee_id"}, table1, {"employee_id"}, "table1", JoinKind.LeftOuter),
    #"Expanded table1" = Table.ExpandTableColumn(#"Merged Queries", "table1", {"anonymize"}, {"table1.anonymize"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded table1", "employee_last_2", each if [employee_id] = [table2.employee_id] then [employee_last] else [table1.anonymize]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"employee_id", "table2.sale_amount", "employee_last_2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"table2.sale_amount", "sales_amount"}, {"employee_last_2", "employee_last"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"sales_amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"employee_id", "employee_last"}, {{"total_sales", each List.Sum([sales_amount]), type nullable number}})
in
    #"Grouped Rows"

Then connect table 1 and table 3 in the data model based on employee_id.

Place the employee_last and total_sales columns from Table 3 in a visual. In the image below I have applied the email address from table 1 as a filter on the visual and you see that only the employee of the selected email address is shown and the rest are anonymous. 

Adams:

Jones:

Now it works through a filter, when you upload the report in server, the table is automatically filtered through the row level security and the filter is no longer needed.

As soon as you upload the report you can enable the row level security on the dataset with the option 'Security'. 

[JERN]

Barthel, thank you for the answer, I really appreciate the advice on the risk associated with the transparent color and I think it works for what I need.  My issue is I'm not well versed in M and I have to build this in to a far more complex report, I do believe I can work my way through it step-by-step to make it work, it will just take a bit of time.