Forum Discussion
Calculate Distinct Key with Multiple Duplicate Line Items
I am trying to calcuate for distinct values where a customer order returns multiple delivery orders, while excluding the packaging IDs.
TABLE 1
| Customer Order # | Delivery Order | Packaging ID | Key |
| 12345 | 5566 | A1 | 123455566 |
| 12345 | 5566 | A2 | 123455566 |
| 12345 | 5565 | A1 | 123455565 |
| 12345 | 5566 | A3 | 123455566 |
| 12345 | 5566 | A4 | 123455566 |
In this example above, I want it to report that Customer Order has 2 Distinct Values but I am struggling with that CALCULATE function. Doing it as a Calculated Column causes elevated row values that are hard to isolate (i.e. Key ID 123455566 would return 4 for each line item)
The Calculate I have tried are not working yet:
=CALCULATE(DISTINCTCOUNT('Table 1'[Key]))
and
var CustomerOrder = 'Table 1'[Key]
return
CALCULATE(COUNT('Table 1'[Customer Order #]),
ALL('Table 1'), 'Table 1'[Key] = CustomerOrder
1 Reply
Hi Justin_Spiegel ,
Why are you using a calculated column the type of calculation you describe would be calculated more efectively on a measure. Making a calculated column you will have the table context that will need additional context.
So depending on where you use the measure you can have the result you need in this case simply use the measure:
Count order =DISTINCTCOUNT('Table 1'[Key])
