Forum Discussion

PivotNovice's avatar
PivotNovice
Frequent Visitor
6 years ago

Filter multiple rows based on the result from another filter

Hi experts. My data is a time series which tracks statuses of a number of different specimens, in just one table, like this:

 

I want to trace the statuses of a subset of specimens over time. For example, if I were to trace the ones with an event during 2015, I would apply filters like this

What I want is a results table (or visual) that looks something like this:

 

This way I can examine a distribution of how long it takes for specimens to die after an event.

 

All of my attempts at making a measure to create this table have failed. Anyone out there up for the challenge?

Need Help

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    This looks like just a measure like NewMeasure = Countrows(Table) + 0 would work in a matrix with Status EOY as Columns and Year as the rows.  Is your example output table match your data?  For example, 2015 and 2019 give expected counts but 2016 does not.  Is a more advanced analysis required that I am missing?

     

    If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • PivotNovice's avatar
      PivotNovice
      Frequent Visitor
      6 years ago

      Thanks for your reponse, mahoneypat. The problem I'm having is that the filters of Year=2015 and Event=D or T filter the data to only include 3 rows, for specimens 3, 4 and 5, and specifically the rows with a Year of 2015. 

       

      I think that I need to create the second visual for the original data to be unfiltered and then refiltered based on those three specimens and Years>=2015.

       

      I'm terrible at DAX but I'm thinking it could be something along the lines of:

      Measure = 
      VAR a = VALUES('Table'[Specimen])
      VAR b = ALL('Table')
      VAR c = CALCULATETABLE(b, [Specimen] in a)
      RETURN c

      which gives errors.

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Your proposal to make a separate table is a good one.  In power query, reference your initial query, remove any columns you don't need (specimen?), and then wrap that in Table.Distinct( ).  I copied your data, called the initial table Status and the distinct version StatusDistinct.  You can then use columns from the distinct table in your slicers or visual to get your desired effect.  I got very close to what you had, but ran out of time.  Use columns from one table in slicers and columns from the other in the matrix visual.

        To use the new table, use the TREATAS() function, with NO relationship between your two tables in a measure like this

         

        RowTreatAs = CALCULATE(COUNTROWS('Status'), TREATAS(VALUES('StatusDistinct'[Event During Year]), 'Status'[Event During Year]), TREATAS(VALUES(StatusDistinct[Status at EoY]), 'Status'[Status at EoY]), TREATAS(VALUES(StatusDistinct[Year]), 'Status'[Year]))
         
        This measure will use the selected values from the distinct table (whether they are in the matrix or in the slicers) and use them as a filter on your original status table.  I hope this helps.
         

        If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat