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.
Hi,
I have 2 queries (NL Sales ZIP) and (NL ZIP list), in this 2nd query I have 3 columns (“ZIP_START”, “ZIP_END” and “TOWN”). I would like to retrieve “TOWN” value from (NL ZIP list) if the value of (NL Sales ZIP) is between “ZIP_START” and “ZIP_END” value of (NL ZIP list) query.
This is way simplified but it’s something like this I should have, I guess:
= Merge queries if value “X” is between “Y” and “Z”
Attached to examples of possible queries.
NL Sales ZIP
NL ZIP list
Solved! Go to Solution.
Hi,
In the NL Sales ZIP Table, add this calculated column formula
=CALCULATE(VALUES('NL Zip List'[TOWN]),FILTER('NL Zip List','NL Zip List'[ZIP Start]<=[ZIPNUM]&&'NL Zip List'[ZIP End]>=[ZIPNUM]))
Hope this helps.
See solution above. Here adjusted for educationl purpose:
let
Table1 = Table.TransformColumnTypes(
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrKwMFTSUXJyVPBLLclILcpJzEspVorVAcoYmoJkQoIxZMzMzIyAMgGeqDKxAA==", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ZIP Num" = _t, #"ZIP Name" = _t])
,{{"ZIP Num", Int64.Type}}),
Table2 = Table.TransformColumnTypes(
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTK0tLQEUo65xSWpRSmJuUqxOtFKRhA5IxOwXFh+TmoeXMoUIgXRFlWemJdalFRalA6WNIboM4VI+iQWpeaBxc2gdhlAaMec3NSiVKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ZIP Start" = _t, #"ZIP End" = _t, #"ZIP Town" = _t])
,{{"ZIP Start", Int64.Type}, {"ZIP End", Int64.Type}}),
#"Merge if > and <" = Table.AddColumn(Table1, "RelativeJoin",
(Earlier) => Table.SelectRows(Table2,
each Earlier[ZIP Num] >= [ZIP Start] and
Earlier[ZIP Num] <= [ZIP End] ) ),
#"Expanded RelativeJoin" = Table.ExpandTableColumn(#"Merge if > and <", "RelativeJoin", {"ZIP Town"}, {"ZIP Town"})
in #"Expanded RelativeJoin"
Reference: https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108
Hi,
In the NL Sales ZIP Table, add this calculated column formula
=CALCULATE(VALUES('NL Zip List'[TOWN]),FILTER('NL Zip List','NL Zip List'[ZIP Start]<=[ZIPNUM]&&'NL Zip List'[ZIP End]>=[ZIPNUM]))
Hope this helps.
Hi there,
I think I have a somewhat similar ask. I'm very new to PBI and DAX so I'm getting lost in the logic but think this can be done!
I want to add a column that returns a numeric value (either 1/0) based on a text value from another query. The two queries contain a unique site ID.
For example, my Query1 contains all my site data and Query2 contains all my project data (5 project types) that occured at those sites. I want to add 5 columns (named for each project type) to Query1 so I can easily summarize what activity has occured at each unique site.
Query2:
SiteABC, Project Type1
SiteABC, Project Type2
SiteDEF, Project Type5
SiteDEF, Project Type3
Query1:
Site ProjectType1 ProjectType2 ProjectType3 ProjectType4 ProjectType5
SiteABC 1 1
SiteDEF 1 1
SiteGHI
SiteJKL
Any help is so very appreciated!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |