cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jasonbakersd Regular Visitor
Regular Visitor

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.   

 

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. 

Then sum the "Amount Paid.

 

In my two tables Member ID is not unique so no relationship can be establishied.

 

Any help appreciated!

 

 

 

exampleForPharm.png

 

 

 

9 REPLIES 9
ChrisMendoza Senior Member
Senior Member

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

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 Regular Visitor
Regular Visitor

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

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 Senior Member
Senior Member

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

@jasonbakersd,

 

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

 

1.PNG

 

2.PNG

4.PNG

3.PNG

 

 

 

 

jasonbakersd Regular Visitor
Regular Visitor

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

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.

 

 

Super User
Super User

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

Hi,

 

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?

 

Please clarify.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xjiin-msft Super Contributor
Super Contributor

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

Hi @jasonbakersd,

 

Try this measure:

 

Sum Amount Paid =
CALCULATE (
    SUM ( Table2[Amount Paid] ),
    FILTER (
        ALL ( Table2 ),
        Table2[Member ID]
            IN SELECTCOLUMNS ( ALLSELECTED ( Table1 ), "Member ID", [Member ID] )
    )
)

11.PNG22.PNG

 

Thanks,
Xi Jin.

jasonbakersd Regular Visitor
Regular Visitor

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

@v-xjiin-msft

 

Thanks for the response.  I've created the measure using similar logic in my model and it's working pretty well.

 

I'm thinking I have to play around with the ALL portion as my results are close (compared to database).   

 

I'll want to be able to use this (and similar measures) on time series charts and other visuals. 

 

I'll dig in a bit more and get back to you.

 

 

v-xjiin-msft Super Contributor
Super Contributor

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

Hi @jasonbakersd,

 

Did you resolve your issue? If so, please kindly mark corresponding reply. It'll benefit others with relevant issue. If not, please feel free to ask.

 

Thanks,
Xi Jin.

Highlighted
jasonbakersd Regular Visitor
Regular Visitor

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

Sorry for the late response (I've been on vacation). 

 

The measure you provide is close, however it's not filtering correctly.   I will try and re-post tomorrow with some additional details.

 

Thanks for the help!

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 425 members 4,257 guests
Please welcome our newest community members: