cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors