Calculate COUNT of DISTINCT values with GROUP BY

[msuser48]

I have two tables, where I am trying to make the right CalculatedColumn in table 2:

Table 1:

Id	Difference
1	1
1	1
2	1
2	1
3	2
3	2

Table 2 (DESIRED OUTPUT TABLE)

Numbers	CalculatedColumn
1	2
2	1
3	0

I need the right code for my "CalculatedColumn" in table 2.

The goal is to count the DISTINCT "Difference" value from table 1 for EACH Id, and add these together.

- So, you can see in table 1, both ID 1 & ID 2 have the distinct value of 1 EACH.

- 1+1 = 2, therefore the calculated column should put 2 as value where "Numbers" column = 1.

My attempt:

Counter =
SWITCH (
TRUE(),
Table2[Numbers] = 1, CALCULATE(SUM(Table1[Difference]),Table1[Difference] = 1),

Table2[Numbers] = 2, CALCULATE(SUM(Table1[Difference]),Table1[Difference] = 2
)

.. and so on.

[v-jialluo-msft · Accepted solution]

^{Hi @msuser48 ,}

^{Please follow these steps:}

^{(1) Create a new column}

Counter =
SWITCH (
TRUE(),
'Table 2'[Number] = 1, CALCULATE(DISTINCTCOUNT('Table 1' [ID]),'Table 1' [Difference] = 1),
'Table 2'[Number] = 2, CALCULATE(DISTINCTCOUNT('Table 1' [ID]),'Table 1' [Difference] = 2),
'Table 2'[Number] = 3, CALCULATE(DISTINCTCOUNT('Table 1' [ID]),'Table 1' [Difference] = 3)
)

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

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