Forum Discussion

bgerew's avatar
bgerew
New Member
8 years ago
Solved

Multi step filter

I have 2 tables.

 

The first table will be filtered by date using a slicer. This will return a list of invoice numbers.

 

I then would like to filter the second table by those invoice numbers that were returned in the first table.

 

Thank You

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi bgerew,

     

    Based on my test, you should be able to use the formula below to create a measure, then use the measure to apply a visual level filter(Measure is great than 0) to filter the second table by selected value in first table.

    Measure =
SUMX (
    Table1,
    IF (
        CONTAINS (
            Table1,
            Table1[Invoice Number], FIRSTNONBLANK ( Table2[Invoice Number], 1 )
        ),
        1,
        0
    )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

3 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    I'm a touch confused - is it possible that your data set has an invoice number more than once that can have different dates inside and outside of your slicer? I'd have thought that they should be unique, and the first slicer should, assuming there's some sort of relevant relationship, that the second table would be filtered in the way you want by default?

    • bgerew's avatar
      bgerew
      New Member
      8 years ago

      the invoice number can not be a managed relationship as it is a many to many relationship.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi bgerew,

     

    Based on my test, you should be able to use the formula below to create a measure, then use the measure to apply a visual level filter(Measure is great than 0) to filter the second table by selected value in first table.

    Measure =
SUMX (
    Table1,
    IF (
        CONTAINS (
            Table1,
            Table1[Invoice Number], FIRSTNONBLANK ( Table2[Invoice Number], 1 )
        ),
        1,
        0
    )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards