Filter a table with values from another table (relationship one to many)

[Lukas_G]

Hi!

I am trying to solve a problem which at first sight looks super simple, but somehow seems to be quite challenging.

I have two tables WorkItem and CustomField with a relationship one to many, see screenshots below.

I would like to filter the WorkItem table for all tasks, that are associated with Team A and Quarter 1, hence only see Task 1.

Using slicers or filters in the visuals doesn't lead to any success.

Any help would be very much appreciated 🙂

[Lukas_G]

Hi @alena2k , thanks for your reply 🙂 

I tried what you suggest, but it unfortunately doesn't do the job.

[alena2k · Accepted solution]

@Lukas_G you are absolutely right! I did not look to the data close enough.

Please transform you CustomField table in PowerQuery as following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQosTSwqSS0CsgyVYnUgYiGpiblAyhEsYIRFkRFCkRNYwBhFkQlcDKrIGSxggqLICC6GsC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemFK = _t, CF_Name = _t, CF_Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemFK", Int64.Type}, {"CF_Name", type text}, {"CF_Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[CF_Name]), "CF_Name", "CF_Value")
in
    #"Pivoted Column"

It will give you table which you can slice by custom field combination.

[Lukas_G]

@alena2k , thank you so much!!! It works 🙂

[alena2k]

Hi @Lukas_G, it sound that you simply need to change Cross filter direction on this relationship from Single to Both.
- double click on relationship in Model view
- at the bottom you will see Cross filter direction dropdown, select Both