Forum Discussion

BeenSearching's avatar
BeenSearching
Frequent Visitor
9 years ago
Solved

Multi value category + Adhoc Filter

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's avatar
    v-caliao-msft
    9 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    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

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    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