Multi value category + Adhoc Filter

[BeenSearching]

I have a list of employees with category fields that define each employee's work type. Employees can have more than one type. Here is an example: 

So far I've been able to find varias ways to make the employee count from each category appear correctly in a bar graph like this:

However, the solutions I've found don't allow me to use the bar chart as a way to ad-hoc filter other visuals. I click on Category1 for example and nothing happens in the other visuals.

How can I build a bar chart that allows me to ad-hoc filter other visuals on the page?

Thanks for any help the community can offer. 

Also, this Solution was very close but relies on a filter visualization. I'm hoping to find a way to make it work using the bar chart. 

[v-caliao-msft · Accepted solution]

@BeenSearching,

You could unpivot this table, and then count the Cateogrys.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgABQyUdJefEktT0/KJKZLYRkK0UqwNVBeahSsLYxghVxhBV2CVNiLLIlCiLzIhSZQ53DkLMAs0V2HVa4nErpnJDA9KU4w/yWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, Category1 = _t, Category2 = _t, Category3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", Int64.Type}, {"Category1", type text}, {"Category2", type text}, {"Category3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee"}, "Attribute", "Value"),
    #"Removed Blank Rows" = Table.SelectRows(#"Unpivoted Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

Regards,

Charlie Liao

[TomMartens]

Hey,

you have to build a many to many relationship, this means you need three tables:

• employee
• category
• employees_categories

build the following relationships:

• category (one) and employees_categories (many) cross filter direction "Single"
• employee (one) and employees_categories (many) cross filter direction "Both"

Hide the table employees_categoriees from report view

Create a measure that counts distinct the employees in the table employees_categories like so

employees count = 
calculate(
  distinctcount('employees_categories'[employee])
)

Hope this helps, if not please create sample data that we can easily recreate, e.g. upload a pbix to OneDrive or Dropbox and share the link