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
v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

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] )
    )
)

 

Thanks,
Xi Jin.

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

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's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.

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

      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!