Forum Discussion

jasonbakersd's avatar
jasonbakersd
Icon for Helper I rankHelper I
8 years ago

How to filter rows in a table based on rows in another table that has been filtered

   I'm trying to figure out how to create a measure that calculates a value in a table based on filtered rows in another table.   Table 1 would be filtered by a slicer to select condition.      ...
Need Help
ChrisMendoza's avatar
ChrisMendoza
Icon for Resident Rockstar rankResident Rockstar
8 years ago

hello jasonbakersd,

 

When you say In my two tables Member ID is not unique..., do mean that 'Table 1'[Member ID] does not equal 'Table 2'[Member ID] or in other words 'Table 1' value of Dozer is not the same as 'Table 2' value of Dozer?

 

Or do you mean that you have mulitple Dozer's in either table and a relationship can't be built because their are duplicate values of Dozer?

 

My guess is you need to build a 'lookup table' of unique [Member ID] then build the relationship between the 'Table 1'[Member ID] (Many:1) -> 'lkupTable'[Member ID] <- (1:Many) 'Table 2'[Member ID] 

  • jasonbakersd's avatar
    jasonbakersd
    Icon for Helper I rankHelper I
    8 years ago

    Tables 1 & 2 have many member ID so it would be a many to many relationship.

     

    There is a member table with relationships to both tables.  

     

     

    • ChrisMendoza's avatar
      ChrisMendoza
      Icon for Resident Rockstar rankResident Rockstar
      8 years ago

      jasonbakersd,

       

      How about Merging the two tables? I chose LEFTOUTER, you may require different with your actual data.

       

       

       

       

       

       

      • jasonbakersd's avatar
        jasonbakersd
        Icon for Helper I rankHelper I
        8 years ago

        These are claims files for Medical and Pharmacy claims.   

        These are fact tables that span 5 years of data and store data different grains so merging isn't an option.   

         

        The requrirements are simple.

         

        Get members from Pharmacy table that have a specific value.

        Use that member group to filter the medical claims to get medical spend and other slices of information.

         

        I've got some Market Basket like measures that do subset filtering on one table, it's just working out the DAX to get the 2nd part of the requirement.