Search code examples
powerbipowerbi-desktoppowerbi-custom-visuals

Irrelevant filtering in Power BI

I have three tables:

  1. "Property" with columns: "PropertyID", "PropertyName", "MarketID", "SubmarketID", etc.
  2. "Market" with columns: "MarketID", "MarketName", etc.
  3. "Submarket" with columns: "SubmarketID", "SubmarketName", etc.

The data model has relationship between Property and Market by MarketID, and Property and Submarket by SubmarketID

I need to select properties by Market and Submarket.

I created slicer to select "Market" by "MarketName" and slicer to select "Submarket" by "SubmarketName".

When I filter Market the slicer "Submarket" still shows the whole list of SubmarketNames but not only values relevant to selected Market.

Moreover, the matrix with main row "MarketName" and next level row "SubmarketName" shows relevant Submarkets for each Market and when I filter any Markets in the slicer shows the relevant subset.

How to fix this issue and see the list of relevant Submarkets only?

Solution

  • Create a Measure like:

    Property count = CALCULATE( DISTINCTCOUNT('Property'[PropertyID]) )

    Add this measure, to both slicers, in Filter pane as a Visual filter, and set it to is not blank.