Group By Issues with Filtering

[Anonymous]

The data set for my Power BI dashboard is used to track products manufactured and defects. Each failure is given a number but within that number there can be multiple defects each unique to a location. To get an accurate defect count I’ve first created a ‘reference table’, grouped by failure number and location then count distinct rows of failure number. It returns the below table which is accurate:

Each defect can be worked on by multiple people, pieces of equipment etc and my issue is when I try to filter it. For example, if I filter it by BOC and person 1 the count of defects is 5 not 2. From some analysis I think it’s because for the failure number for person 1 has worked on there are also 3 other entries with the same failure numbers but different people:

In the data modelling tab I’ve created a many to many relationship between the failure number in this reference table and the failure number in the main data table. Hopefully I’ve shared enough information here because I’m totally stuck.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak This is as much as I feel I can share. What else would help understand the problem?

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

If you want to get a dynamic count of location per Failure Number, build a column(No. of defects) doesn't work.(it's just a count by gourp by Failure Number and result is not dynamic)

We don't need to build a reference table. You can try count function in table visual or build a measure.

I build a sample table like yours.

My table visual is built by Failure Number and Location(select count)

Or Measure:

Measure = COUNT('Main Data'[Failure Number])

Build Failure Number and Person slicers. Let's have a test.

Result is as below. We see count function and measure works well but No. of defects(in reference table) shows wrong result.

You can download the pbix file from this link: Group By Issues with Filtering

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.