Creating a measure between column of separate linked tables that can be filtered

[Anonymous]

I have two tables, DEFECTS and SAMPLE that I've created simple examples of below.

The DEFECTS table has a record for each defect recorded along with info about the defect and a key to connect it to the SAMPLE table.  The key is a combination of the defect descriptors.

Date	Shift	Model	Key
2/24/2020	1	A	202002241A
2/24/2020	2	A	202002242A
2/24/2020	2	B	202002242B
2/25/2020	1	A	202002251A
2/25/2020	1	B	202002251B
2/25/2020	1	A	202002251A
2/25/2020	2	A	202002252A
2/25/2020	2	A	202002252A
2/25/2020	2	B	202002252B
2/25/2020	2	B	202002252B

The SAMPLE table has a record for each date/shift/model and the volume (Sample) that was produced.

Date	Shift	Model	Sample	Key
2/24/2020	1	A	20	202002241A
2/24/2020	1	B	15	202002241B
2/24/2020	2	A	21	202002242A
2/24/2020	2	B	13	202002242B
2/25/2020	1	A	19	202002251A
2/25/2020	1	B	18	202002251B
2/25/2020	2	A	22	202002252A
2/25/2020	2	B	11	202002252B

I'm trying to create a measure that is DPU (defects per unit produced) so that I can plot it by date.  I want to be able to slice/filter the plot by shift and model.  The DPU is calculated by dividing the number of defects by the total units produced (Sample).  As I filter for shift and model, I need the defect count and the sample size to change accordingly.

I think I need something like what I've shown below, but I can't get the FILTER function to allow me to use a boolean expression that uses columns from two different tables.

DPUMeasure = CALCULATE(COUNT(DEFECTS[Date] / SUM(SAMPLE[Sample]), FILTER(DEFECTS[Key] = SAMPLE[Key]))

[amitchandak]

Try that you create a common key table . Or can sample act as master 

New column in sample 
//add DEFECTS[shift] = SAMPLE[shift] optional
Total defect in sample table = countx(filter(DEFECTS[Key] = SAMPLE[Key]),DEFECTS[Date])

New measure when key is master
averagex(summarize(Key[Key],"_cnt",COUNT(DEFECTS[Date]),"_sum",SUM(SAMPLE[Sample])),divide([_cnt],[_sum]))

New measure when sample is master
averagex(summarize(SAMPLE[Key],"_cnt",COUNT(DEFECTS[Date]),"_sum",SUM(SAMPLE[Sample])),divide([_cnt],[_sum]))

[Anonymous]

Amit,

I'm having trouble following the first option you gave where I create a common key table and use key as master.  I don't know what you mean by creating a common key table.

I tried the second option where sample is master.  It gives the same result as Greg_Deckler's solution.  I replied to him with the results (shown below).

"I tried creating your measure, but it doesn't seem to be calculating correctly.

For example, DPU for 2/24/2020 should be 0.04 (3 defects / 69 units) and for 2/25/2020 should be 0.10 (7 defects / 70 units).  However, the measure gives me 0.02 for 2/24/2020 and 0.05 for 2/25/2020.

Then when I filter/slice by shift 1, DPU for 2/24/2020 should be 0.03 (1 defects / 35 units) and for 2/25/2020 should be 0.08 (3 defects / 37 units).  However, the measure gives me 0.01 for 2/24/2020 and 0.02 for 2/25/2020.

I think the problem is that the final DPU calculation is still dividing by the total sample for both days (139) regardless of how I plot it (say by date) or filter/slice it."

[Anonymous]

I think I may be close.  I tried:

DPU = CALCULATE(COUNTROWS(DEFECTS) / SUM('SAMPLE'[Sample]), FILTER(DEFECTS, RELATED('SAMPLE'[Key]) = DEFECTS[Key]))

It gives me the correct answer for most scenarios but not all.

[Anonymous]

I figured out why this formula won't work in all situations, but I don't know how to solve the issue.

DPU = CALCULATE(COUNTROWS(DEFECTS) / SUM('SAMPLE'[Sample]), FILTER(DEFECTS, RELATED('SAMPLE'[Key]) = DEFECTS[Key]))

This formula is summing the sample size based on the key from defects.  So if there is no defect for a particular date/shift/model, it won't include that sample in the sum even though it is needed for the calculation.

Any ideas?  I'm thinking I may have to create some kind of user inputs that become variables to sum up the correct sample size.

[Greg_Deckler]

I did it like this but this currently depends on a 1:many relationship between the tables which if that is not the case I think is still fixable.

DPU = 
    VAR __Table = 
        ADDCOLUMNS(
            GROUPBY(
                'SAMPLE',
                [Key],
                "__Sum",SUMX(CURRENTGROUP(),[Sample])
            ),
            "__Defects",COUNTROWS(RELATEDTABLE(DEFECTS))
        )
RETURN
    DIVIDE(
        SUMX(__Table,[__Defects]),
        SUMX(__Table,[__Sum]),
        0
    )

 PBIX attached. 

[Anonymous]

Greg,

I tried creating your measure, but it doesn't seem to be calculating correctly.

For example, DPU for 2/24/2020 should be 0.04 (3 defects / 69 units) and for 2/25/2020 should be 0.10 (7 defects / 70 units).  However, the measure gives me 0.02 for 2/24/2020 and 0.05 for 2/25/2020.

Then when I filter/slice by shift 1, DPU for 2/24/2020 should be 0.03 (1 defects / 35 units) and for 2/25/2020 should be 0.08 (3 defects / 37 units).  However, the measure gives me 0.01 for 2/24/2020 and 0.02 for 2/25/2020.

I think the problem is that the final DPU calculation is still dividing by the total sample for both days (139) regardless of how I plot it (say by date) or filter/slice it. 

[Greg_Deckler]

So these tables are not related?

Can you create a bridge table of unique values and relate them?

[Anonymous]

I have the tables related by the Key column in each table.  Defects[Key]:Sample[Key] as a Many:1 connection.

[Greg_Deckler]

@Anonymous - OK, then we should be good then?