Why is my measure not respecting a filter?

[Anonymous]

I have three tables:

two dimensions:

- Products

- Customers

one fact table:

- Orders

I'm trying to create a table, that shows a row for each product. As metrics I'd like to have a measure for the unique amount of customers that ordered the product, the total amount of customers and the percentage of people that bought the product.

So on the customer table I created a measure "# Customers := COUNTROWS(Customers)"

On the product table, I creates a measure "# Customers ordered := DISTINCTCOUNT(Orders[Customer ID])"

And lastly, a measure that calculates the percentage: "% Customers ordered := [# Customers ordered] / [# Customers]"

When I put this in the table, it works fine. The problems arise when I try to filter the customer table to show only female customers. While the # customers correctly decreses, the amount of # customers ordered, remains the same. It seems that the measure counts any disctinct customer ID, without respecting the filter on gender.

Any ideas what could be wrong? The above approach is a simplified version of reality by the way, there are more relationships in the model.

Thanks for your help.

[v-yuta-msft · Accepted solution]

Hi bo-oz ,

According to what you have said, you are using a slicer based off columns in fact table and expect the measure shows correct result based on the slicer, right? Suppose you have one-many relationship built between your dimensional table and fact table. You should use allselected() to apply the filter to your measure like pattern below:

# Customers :=
CALCULATE ( COUNTROWS ( Customers ), ALLSELECTED ( Customers ) )

Regards,

Jimmy Tao