Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Why is my measure not respecting a filter?

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.

 
1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.