Forum Discussion

Lukas_G's avatar
Lukas_G
New Member
3 years ago
Solved

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

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 🙂

Need Help
  • alena2k's avatar
    alena2k
    3 years ago

    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.

4 Replies

  • alena2k's avatar
    alena2k
    Icon for Resolver IV rankResolver IV
    3 years ago

    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

  • Lukas_G's avatar
    Lukas_G
    New Member
    3 years ago

    Hi alena2k , thanks for your reply 🙂 

     

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

     

    • alena2k's avatar
      alena2k
      Icon for Resolver IV rankResolver IV
      3 years ago

      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.