Forum Discussion

KelvinMorel's avatar
KelvinMorel
Icon for Helper II rankHelper II
7 years ago
Solved

Retrieve value from queries if condition applies

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.

    • Carlucha28's avatar
      Carlucha28
      Frequent Visitor
      7 years ago

      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!

  • Ola_S's avatar
    Ola_S
    Frequent Visitor
    7 years ago

    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