Reference Table Group By and 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.

These screenshots are as much as I feel I can share but hopefully it's enough.

[AlB]

Hi @Anonymous 

Many-to-many relationships are very tricky and should be avoided in general unless you know very well what you are doing. That said, I have not completely understood what you are trying to do: filter the second table and count the number of rows that comply with certain conditions? Can you provide an example of the original situation and the expected result?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB do you think it's in the Many-to-many relationship that is causing the issues.

The second table is the raw data where all the variables relating to a failure are stored. This screenshot of the graph I'm using to display the data (filtered) shows what I mean about the wrong count for BOC. 

 

[v-deddai1-msft]

Hi @Anonymous ,

If you have group your raw table to ‘reference table' table by failure number. Failure number will be unique in ‘reference table'. And you can create one to many relationship between them. 

And did you use No.of Defects column in your ‘reference table' for values in your chart? you need to use measure :

measure = DISTINTCOUNT('Table'[Location]])

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

Best Regards,

Dedmon Dai