cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Calculate COUNT of DISTINCT values with GROUP BY

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.

1 ACCEPTED SOLUTION
Community Support

Hi @msuser48 ,

(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.

Community Support

Hi @msuser48 ,

(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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors