Forum Discussion

Tanyag's avatar
Tanyag
Frequent Visitor
5 years ago
Solved

Filtering Data based on another non related field from a different Table

I have two tables and oen is 

and another is 

I can select the distance using ZIP code and show  the Zip Codes within the selected radious . My issues is I would like to see the data from second table based on Zip Code within that radius.  The Second Table is using direct query and cannot use API function Is there a workaround for this using a DAX/PowerQuery 

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Tanyag ,

     

    To better understand your scenario, I have some questions:

     

    1. Are there no realtionships between the two tables?
    2.  Which fields do you use for the slicers of "Zip" and "Maximum Distance (km)"?

     

    If there are no relationships between the two tables and the "Zip" slicer is based on 'Table1'[zip code], the "Maximum Distance (km)" is based on a What-if parameter, try to create a measure like below and set it as "is 1" when using it as a visual level filter.

     

    Measure =
VAR SelectedZip =
    SELECTEDVALUE ( 'Table1'[zip code] )
VAR SelectedRadious = [Maximum Distance (km)]
RETURN
    IF (
        MAX ( 'Table2'[zip] ) = SelectedZip
            && MAX ( 'Table2'[DistanceMI] ) <= SelectedRadious,
        1
    )

     

    If this doesn't meet your requirements, please show me more details.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Tanyag ,

     

    To better understand your scenario, I have some questions:

     

    1. Are there no realtionships between the two tables?
    2.  Which fields do you use for the slicers of "Zip" and "Maximum Distance (km)"?

     

    If there are no relationships between the two tables and the "Zip" slicer is based on 'Table1'[zip code], the "Maximum Distance (km)" is based on a What-if parameter, try to create a measure like below and set it as "is 1" when using it as a visual level filter.

     

    Measure =
VAR SelectedZip =
    SELECTEDVALUE ( 'Table1'[zip code] )
VAR SelectedRadious = [Maximum Distance (km)]
RETURN
    IF (
        MAX ( 'Table2'[zip] ) = SelectedZip
            && MAX ( 'Table2'[DistanceMI] ) <= SelectedRadious,
        1
    )

     

    If this doesn't meet your requirements, please show me more details.

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Tanyag's avatar
      Tanyag
      Frequent Visitor
      5 years ago

      Thanks Icey

      (sorry for delayed response I was on a walk)

      As Far as relationship goes the common key field is ZIP code 

      The Zip I used is global city data (Open Source) It eventually is working