filter upstream table to only retrieve records where downstream records exist

Question

I am trying to find products that my companies stores in multiple facilities. I have a that shows the unique:

State
City
Location

And a fact table that shows the Inventory details by location ID . The location in Inventory corresponds to the Location in the locations table and allows me to bring in the actual city/state of the inventory.

I am trying to create a measure that will give me a count of how many States a product is associated with (essentially, how many states is the inventory item in, based on location).

I wrote a similar measure that shows how many unique locations the item is in:

Count of Location = 
    COUNTROWS(
        DISTINCT(inventory_details[LOCATION])
    )

My approach for the Count of State measure doesn't work beacuse the table is upstream:

Count of State = 
        COUNTROWS(
            DISTINCT(locations[STATE])
        )

This screenshot shows that the relationships and data are established correctly and when a particular product is in the scope of a matrix, I can see what the balance is, by State

This screenshot shows the usage of my Count of Location and Count of State measures. THe count of location measure works as intended but as you can see, the Count of State measure returns all available states since the higher level (upstream) table is not being filtered by the product in scope.

Any insight into how to approach this in DAX would be greatly appreciated.

Answer 1

Add CALCULATE to your measure:

Count of State = 
CALCULATE(
        COUNTROWS(
            DISTINCT(locations[STATE])
        )
)