Forum Discussion

JacobMotu's avatar
JacobMotu
Frequent Visitor
3 years ago
Solved

Filter slicer values based on multiple conditions

Download pbix: This is the report with close-to-original data + data model.

Goal: I want to filter values that appear in my Block slicer in a way that there are only the blocks that have more than one data point in the charts = have data for more than one calendar year within the range selected in slicer Year.

Example: Crop = Kiwifruit, Property = Alister, Block = Grey St, Year = 2018-2023 -> This one should not be visible in the Block slicer. At the same time, all the other slicers on the page should normally affect the Block slicer as well (that one is OK).



I wanted to use IF or SWITCH against a number of distinct years associated with each Block, then probably check that against a number of selected years in Year slicer. Unfortunately didn't get even close (see HasMultipleYears and Years in Samples).

I appreciate some help. I'm still new to all this, and learning 🙂

Need Help
  • JacobMotu's avatar
    JacobMotu
    3 years ago

    I solved that with this measure. I use the measure to filter the Block slicer (is greater than 1).

    2DataPointsSoilBlock = COUNTROWS(
  FILTER(
    SUMMARIZE(
      tblSoilDetail,
      tblSoilDetail[Block],
      tblSamples[Year]
    ),
    [Block] <> BLANK() && [Year] <> BLANK()
  )
)

1 Reply

  • JacobMotu's avatar
    JacobMotu
    Frequent Visitor
    3 years ago

    I solved that with this measure. I use the measure to filter the Block slicer (is greater than 1).

    2DataPointsSoilBlock = COUNTROWS(
  FILTER(
    SUMMARIZE(
      tblSoilDetail,
      tblSoilDetail[Block],
      tblSamples[Year]
    ),
    [Block] <> BLANK() && [Year] <> BLANK()
  )
)