How do I filter to a specific category, and mask/anonymize the remaining categories?

Question

I have the following data:

Person    Time   Percent
Bert      Q1     .10
Bert      Q2     .12
Bert      Q3     .13
Bert      Q4     .15
Bill      Q1     .08
Bill      Q2     .09
Bill      Q3     .10
Bill      Q4     .10
Bob       Q1     .02
Bob       Q2     .04
Bob       Q3     .06
Bob       Q4     .08
Brian     Q1     .01
Brian     Q2     .03
Brian     Q3     .06
Brian     Q4     .10

I've created a line chart in Power BI showing each person's percent over time.

I also have an alias table that anonymizes each person:

Person     Alias
Bert       Person 1
Bill       Person 2
Bob        Person 3
Brian      Person 4

What I would like to do is add a filter that allows me to select a person, and then anonymize the other people. For example, if I want to view all of Bob's data, I select Bob, so I can see Bob's Percent over Time. Then I'll be able to see 'Bert', 'Bill', and 'Brian's data, however, I don't want to know, who is who, I would only like to see their Alias name.

I've seen posts that are looking to anonymize all the data on a page, but I haven't come across a dynamic filtering that allows me to do exactly what I'm looking for.

Thank you for your help.

user_765226 · Answer

This is a bit tricky since you can't create calculated tables dynamically.

Here's one way to accomplish this though.

Create a new table for your chart's Legend that combines both Person and Alias.

Legend = UNION ( VALUES ( Alias[Person] ), VALUES ( Alias[Alias] ) )

Write a measure that calculates for either the selected Alias[Person] or the aliases not associated with that person. Basically, create a chart that shows each person and each alias but blank out the ones we don't want to see.

Measure = 
VAR SelectedPerson = SELECTEDVALUE ( Alias[Person] )
VAR LegendPerson = SELECTEDVALUE ( Legend[Person] )
RETURN
    IF (
        LegendPerson = SelectedPerson,
        CALCULATE ( SUM ( Data[Percent] ), Alias[Person] = LegendPerson ),
        CALCULATE (
            SUM ( Data[Percent] ),
            FILTER ( ALL ( Alias ), Alias[Person] <> SelectedPerson ),
            Alias[Alias] = LegendPerson
        )
    )

In this measure, the SelectedPerson is the one you select via the Alias[Person] slicer and LegendPerson is the local filter context in the line chart. When they are the same, we match on the Alias[Person] column, otherwise on the Alias[Alias] column (after filtering out the alias corresponding to the selected person).

The result should look like this when you select a person from the alias table:

user_3652345 · Answer

Power BI Line chart do not allow Measures in the Legend property and as a result there are no straight forward solution for your requirement. You can prepare your base data in Power Query Editor applying some transformation steps as stated below-

Let your base table name is - anon1 and your person dictionary table name is - anon2

Step-1: Edit your anon1 code using **advanced editor" and use this following code. Edit table names with anon1 and anon2 with your original table names. In the below code, from #"Merged Queries" step is the transformation steps I started from. Before two steps are for Base table and no changes required in the base table steps (may be more steps there in your table) and just start adding steps after your existing steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc4xCoAwDIXhu3QukjZRdPUGztJBwaFQFIr3R0m1TZYM3/DnrauZj3wbaxb3ns6BCbaZZ/PKkA2VEVtfLKZUezAq4x5MylD+/YykXXvLeUmlRpI4BoMkkjty3M5WcxpLDzWq4o91X3gA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Time = _t, Percent = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Time", type text}, {"Percent", type number}}),

    //-- Here from new transformation starts
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Person"}, anon2, {"Person"}, "anon2", JoinKind.LeftOuter),
    #"Expanded anon2" = Table.ExpandTableColumn(#"Merged Queries", "anon2", {"Alias"}, {"anon2.Alias"}),
    #"Added Custom" = Table.AddColumn(#"Expanded anon2", "Custom", each anon2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Person", "Alias"}, {"Custom.Person", "Custom.Alias"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [Person] = [Custom.Person] then [Person] else[anon2.Alias])
in
    #"Added Custom1"

The output will be as below-

Step-2: Get back to report by clicking Close and Apply button.

Step-3: Create the slicer using column Custom.Person

Step-4: Now configure the Line chart as below-

And finally, here is the output-

How do I filter to a specific category, and mask/anonymize the remaining categories?

2 Answers