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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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