Filter multiple rows based on the result from another filter

[PivotNovice]

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?

[mahoneypat]

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]

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]

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

[PivotNovice]

Thank you again. I'm going to play around with this a bit to undertstand it. I've never seen the TREATAS function before. Doing what you said verbatim is still not quite delivering the goods, as all I got on the second table is the first table but where the filters do not act upon it.

Where what I need is to create the second table as if it has filters acting upon it which are specimens 3, 4 and 5 and years>=2015, which I can do here manually, like this:

And if there's any way this can be done without duplicating the table in power query, that would be preferred as my actual data is quite large.

Thanks for your efforts so far, though!

[V-lianl-msft]

Hi @PivotNovice ,

Is that what you expect?

Create an unrelated year table as slicer.

Table 2 = DISTINCT('Table'[Year])

Create a measure and apply it to the visual level filter of the first visual.

Measure = IF(MAX('Table'[Year])IN VALUES('Table 2'[Year]),1)

Create the following measure and drag it into the value field of the matrix.

Measure 2 = CALCULATE(COUNT('Table'[Status at EoY]),FILTER('Table','Table'[Specimen]>=3&&'Table'[Specimen]<=5&&'Table'[Year]>=SELECTEDVALUE('Table 2'[Year])))

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PivotNovice]

Thank you so much, @V-lianl-msft. While this still does not do what I need it to, I think you've given me another idea to try with DISTINCT.

The filter that is applied to Table (year=2015, event=T) results in three rows for which specimens are 3, 4 and 5. What I need is to then use those specimens within Measure 2. Your definition of Measure 2 includes 'Table'[Specimen]>=3&&'Table'[Specimen]<=5 which does not allow for the dynamic filtering I seek.

Sadly, I've spent so long on this, I may just have to take a different approach. Like Excel. 😞