Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 🙂
Solved! Go to 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.
Hi @alena2k , thanks for your reply 🙂
I tried what you suggest, but it unfortunately doesn't do the job.
@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.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.