Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jasonbakersd
Helper I
Helper I

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
v-xjiin-msft
Solution Sage
Solution Sage

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.

@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.

 

 

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.

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!

 

 

Ashish_Mathur
Super User
Super User

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/
ChrisMendoza
Resident Rockstar
Resident Rockstar

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] 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.  

 

 

@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

 

 

 

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.