Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Lukas_G
New Member

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.

Lukas_G_0-1667316109404.png

Lukas_G_1-1667316176372.png

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 🙂

1 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"

 

1.png

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

View solution in original post

4 REPLIES 4
Lukas_G
New Member

Hi @alena2k , thanks for your reply 🙂 

 

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

Lukas_G_0-1667328100428.pngLukas_G_1-1667328121975.png

 

@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"

 

1.png

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

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

alena2k
Resolver IV
Resolver IV

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.