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....
Need Help
V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

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's avatar
PivotNovice
Frequent Visitor
6 years ago

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