Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
44 | |
41 | |
39 | |
37 |