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 ...
  • 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

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