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

Question

&nbsp;&nbsp;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.&nbsp;Table 1 would be filtered by a slicer to select condition.&nbsp; &nbsp;&nbsp;Using the Member ID's from Table 1, I want to filter Table 2 to get only those Member ID's that are in the Table 1 filtered row.&nbsp;Then sum the "Amount Paid.&nbsp;In my two tables Member ID is not unique so no relationship can be establishied.&nbsp;Any help appreciated!&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

chrismendoza · Answer

hello&nbsp;jasonbakersd,&nbsp;When you say&nbsp;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?&nbsp;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?&nbsp;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) -&gt; 'lkupTable'[Member ID] &lt;- (1:Many) 'Table 2'[Member ID]&nbsp;

jasonbakersd · Answer

Tables 1 &amp; 2 have many member ID so it would be a many to many relationship.&nbsp;There is a member table with relationships to both tables.&nbsp;&nbsp;&nbsp;&nbsp;

chrismendoza · Answer

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

jasonbakersd · Answer

These are claims files for Medical and Pharmacy claims.&nbsp; &nbsp;These are fact tables that span 5 years of data and store data different grains so merging isn't an option.&nbsp; &nbsp;&nbsp;The requrirements are simple.&nbsp;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.&nbsp;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.&nbsp;&nbsp;

ashish_mathur · Answer

Hi,&nbsp;In Table 1, can there be a case where for Member ID 1, the Condition is Diabetes i.e. can there be a different condition for the same Member ID in Table 1?&nbsp;Please clarify.

Forum Discussion

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

9 Replies

Related content

Filter

Filter depending on another filter

Slicer Filtering Another slicer

Dynamic Filter

Inverse Filter